SQLSERVER聚集索引与非聚集索引的再次研究（上）上篇主要说聚集索引下篇的地址：SQLSERVER聚集索引与非聚集索引的再次研究（下）由于本人还是SQLSERVER菜鸟一枚，加上一些实验的逻辑严谨性，单写《SQLSERVER聚集索引与非聚集索引的再次研究（上）》就用

SQLSERVER聚集索引与非聚集索引的再次研究（上）

上篇主要说聚集索引

下篇的地址：SQLSERVER聚集索引与非聚集索引的再次研究（下）

由于本人还是SQLSERVER菜鸟一枚，加上一些实验的逻辑严谨性，

单写《SQLSERVER聚集索引与非聚集索引的再次研究（上）》就用了12个小时，两篇文章加起来最起码写了20个小时，

本人非常非常用心的努力完成这两篇文章，希望各位看官给点意见o(∩_∩)o

为了搞清楚索引内部工作原理和结构，真是千头万绪，这篇文章只是作为参考，里面的观点不一定正确

有一些问题，msdn里，网上的文章里，博客园里都有提到，但是这些问题的答案是正确的吗？其实有时候我自己都想知道答案

比如，画聚集索引的图，有一些人用表格来表示，但是他们正确吗？

以前知道聚集索引非聚集索引是B树二叉树结构，又知道执行计划图标很像二叉树很传神，但是还是觉得很抽象

这篇文章写完以后还是比较抽象但是最起码比以前清晰一些了

SQLSERVER聚集索引与非聚集索引的再次研究（上）

有很多问题不知道为什么，但是MSDN就是这样说的，既然说得这麽模糊不如自己做一下实验，验证一下MSDN的内容吧o(∩_∩)o

--------------------------------------------华丽的分割线---------------------------------------------

先来看一下索引的结构，文章里面的一些结构图都是自己画的一些草图，本人自认画得非常烂，希望各位看官谅解o(∩_∩)o

SQLSERVER聚集索引与非聚集索引的再次研究（上）

----------------------------------------------华丽的分割线---------------------------------------------------------

先创建一个表，保存DBCC IND的结果

<span> 1</span> <span>CREATE</span> <span>TABLE</span><span> DBCCResult (
</span><span> 2</span> PageFID <span>NVARCHAR</span>(<span>200</span><span>),
</span><span> 3</span> PagePID <span>NVARCHAR</span>(<span>200</span><span>),
</span><span> 4</span> IAMFID <span>NVARCHAR</span>(<span>200</span><span>),
</span><span> 5</span> IAMPID <span>NVARCHAR</span>(<span>200</span><span>),
</span><span> 6</span> ObjectID <span>NVARCHAR</span>(<span>200</span><span>),
</span><span> 7</span> IndexID <span>NVARCHAR</span>(<span>200</span><span>),
</span><span> 8</span> PartitionNumber <span>NVARCHAR</span>(<span>200</span><span>),
</span><span> 9</span> PartitionID <span>NVARCHAR</span>(<span>200</span><span>),
</span><span>10</span> iam_chain_type <span>NVARCHAR</span>(<span>200</span><span>),
</span><span>11</span> PageType <span>NVARCHAR</span>(<span>200</span><span>),
</span><span>12</span> IndexLevel <span>NVARCHAR</span>(<span>200</span><span>),
</span><span>13</span> NextPageFID <span>NVARCHAR</span>(<span>200</span><span>),
</span><span>14</span> NextPagePID <span>NVARCHAR</span>(<span>200</span><span>),
</span><span>15</span> PrevPageFID <span>NVARCHAR</span>(<span>200</span><span>),
</span><span>16</span> PrevPagePID <span>NVARCHAR</span>(<span>200</span><span>)
</span><span>17</span> )

Copy after login

创建一个聚集索引表

<span>1</span> <span>--</span><span>只有聚集索引</span>
<span>2</span> <span>CREATE</span> <span>TABLE</span><span> Department(
</span><span>3</span>     DepartmentID <span>int</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>NOT</span> <span>NULL</span> <span>PRIMARY</span> <span>KEY</span><span>,
</span><span>4</span>     Name <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>5</span>     GroupName <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>6</span>     Company <span>NVARCHAR</span>(<span>300</span><span>),
</span><span>7</span>     ModifiedDate <span>datetime</span> <span>NOT</span> <span>NULL</span>  <span>DEFAULT</span> (<span>getdate</span><span>())
</span><span>8</span> )

Copy after login

插入10W条记录

<span>1</span> <span>INSERT</span> <span>INTO</span> Department(name,<span>[</span><span>Company</span><span>]</span>,groupname) <span>VALUES</span>(<span>'</span><span>销售部</span><span>'</span>,<span>'</span><span>中国你好有限公司XX分公司</span><span>'</span>,<span>'</span><span>销售组</span><span>'</span><span>)
</span><span>2</span> <span>GO</span> <span>100000</span>

Copy after login

将DBCC IND的结果放入DBCCRESULT表

<span>1</span> <span>INSERT</span> <span>INTO</span> DBCCResult <span>EXEC</span> (<span>'</span><span>DBCC IND(pratice,Department,-1) </span><span>'</span>)

Copy after login

查询Department表中的页面情况

先说明一下：

PageType 分页类型： 1：数据页面；2：索引页面；3：Lob_mixed_page；4：Lob_tree_page；10：IAM页面

IndexID 索引ID： 0 代表堆, 1 代表聚集索引, 2-250 代表非聚集索引，大于250就是text或image字段

红色框部分都是需要关注的

SQLSERVER聚集索引与非聚集索引的再次研究（上）

第一个：IAM页不是只有堆表才有也不只是维护堆表中的数据页的连续，有索引的表都有，所以IAM页不只维护数据页，也维护索引页的连续，在下篇说到非聚集索引的时候

我会给出MSDN的解释和IAM页在聚集索引表，非聚集索引表中的情况

第二个：每个数据页的IndexID都是1，不是说数据页变成了索引页，而是说现在数据页已经属于聚集索引的一部分，不在堆里了

第三个：每个数据页的IndexLevel都是0，就是说数据页在聚集索引的最下层

第四个:索引页和数据页,前一页和后一页是首尾相连的,但是数据页和索引页不是首尾相连的，也就是说没有一个数据页的[PrevPagePID]指向14464页或3528页

那么在上面的聚集索引图片中为什麽会说索引页指向数据页呢？叶子节点就是数据页呢？

数据页的index level是0，那么就是说聚集索引的叶子节点就是数据页

SQLSERVER聚集索引与非聚集索引的再次研究（上）

上面索引页的结构

SQLSERVER聚集索引与非聚集索引的再次研究（上）

现在来看一下索引页里都有什么，运行下面的SQL语句

<span> 1</span> <span>DBCC</span> TRACEON(<span>3604</span>,<span>-</span><span>1</span><span>)
</span><span> 2</span> <span>GO</span>
<span> 3</span> 
<span> 4</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>3527</span>,<span>3</span><span>)
</span><span> 5</span> <span>GO</span>
<span> 6</span> 
<span> 7</span> 
<span> 8</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>3528</span>,<span>3</span><span>)
</span><span> 9</span> <span>GO</span>
<span>10</span> 
<span>11</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>14464</span>,<span>3</span><span>)
</span><span>12</span> <span>GO</span>

Copy after login

SQLSERVER聚集索引与非聚集索引的再次研究（上）

您们应该看到ChildPageId，所以上面我的图为什麽会这样画的原因，索引页连接着数据页，而且一个索引页指向多个数据页

SQLSERVER聚集索引与非聚集索引的再次研究（上）

DepartmentID是主键列，从1开始自增，那么从下图可以看出主键列数据是从最左边的索引节点（不是叶子节点）开始排序

SQLSERVER聚集索引与非聚集索引的再次研究（上）

这里有个问题：为什麽根节点只有两行？？？是不是根节点只作连接作用，所以只有两行，不过这个问题我也不清楚

SQLSERVER聚集索引与非聚集索引的再次研究（上）

聚集索引页里主键列DepartmentID上一行与下一行相差120条记录,一个数据页刚好容纳120条记录

KeyHashValue根据主键列的第一个字段而生成的，就算两个表完全一样，这个hash出来的KeyHashValue都不会一样

我创建了一个一模一样的表Department2，看到hash出来的值都不一样

SQLSERVER聚集索引与非聚集索引的再次研究（上）

而这个KeyHashValue我们就叫做键，也就是key-value中的key

SQLSERVER聚集索引与非聚集索引的再次研究（上）

------------------------------------------------------------华丽的分割线------------------------------------------------------

聚集索引怎麽找记录的？？？

这里要分两种情况：（1）聚集索引查找和（2）聚集索引扫描

（1）聚集索引查找

放大一下索引页

SQLSERVER聚集索引与非聚集索引的再次研究（上）

SQLSERVER首先把每个数据页的头一条记录里的DepartmentID的值加上一定范围值hash出一个key值，然后放在KeyHashValue列里

当我要找DepartmentID为110的那条记录里的GroupName和Company的值的时候，首先SQLSERVER根据where DepartmentID=110

将110加上一个范围值hash出一个值，这个值就是KeyHashValue的值，找到KeyHashValue=(f000ff86397c)的那条记录

然后到数据页13791里找出DepartmentID为110的那条记录里的GroupName和Company的值

其实这里的算法应该跟hash join是一样的，但是实际具体怎麽算的？本人就不清楚了，大家可以看一下hash join的原理

个人感觉在SQLSERVER里 key-value hash桶用途很广泛，执行计划、 hash join、聚集索引都用到了

证明：这里我可以证明一下SQLSERVER聚集索引查找记录的流程

先到索引页里找到键值为KeyHashValue=XXX的那条记录，然后再到数据页里把实际数据读出来

运行下面的SQL语句，看一下SQLSERVER申请的锁就知道了

下面实验我在Department2表里做的，表数据和表结构和Department1一模一样

SQLSERVER聚集索引与非聚集索引的再次研究（上）

<span> 1</span> <span>--</span><span>只有聚集索引</span>
<span> 2</span> <span>CREATE</span> <span>TABLE</span><span> Department2(
</span><span> 3</span>     DepartmentID <span>int</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>NOT</span> <span>NULL</span> <span>PRIMARY</span> <span>KEY</span><span>,
</span><span> 4</span>     Name <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 5</span>     GroupName <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 6</span>     Company <span>NVARCHAR</span>(<span>300</span><span>),
</span><span> 7</span>     ModifiedDate <span>datetime</span> <span>NOT</span> <span>NULL</span>  <span>DEFAULT</span> (<span>getdate</span><span>())
</span><span> 8</span> <span>)
</span><span> 9</span> 
<span>10</span> <span>INSERT</span> <span>INTO</span> Department2(name,<span>[</span><span>Company</span><span>]</span>,groupname) <span>VALUES</span>(<span>'</span><span>销售部</span><span>'</span>,<span>'</span><span>中国你好有限公司XX分公司</span><span>'</span>,<span>'</span><span>销售组</span><span>'</span><span>)
</span><span>11</span> <span>GO</span> <span>100000</span>
<span>12</span> 
<span>13</span> 
<span>14</span> <span>SELECT</span> <span>*</span> <span>FROM</span><span> Department2
</span><span>15</span> 
<span>16</span> <span>--</span><span>先清空[DBCCResult]表里的记录</span>
<span>17</span> <span>--</span><span>TRUNCATE TABLE [dbo].[DBCCResult]</span>
<span>18</span> <span>INSERT</span> <span>INTO</span> DBCCResult <span>EXEC</span> (<span>'</span><span>DBCC IND(pratice,Department2,-1) </span><span>'</span><span>)
</span><span>19</span> 
<span>20</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>DBCCResult</span><span>]</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>PageType</span><span>]</span> <span>DESC</span> 
<span>21</span> 
<span>22</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>14471</span>,<span>3</span><span>)
</span><span>23</span> <span>GO</span>
<span>24</span> 
<span>25</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>4375</span>,<span>3</span><span>)
</span><span>26</span> <span>GO</span>
<span>27</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>4376</span>,<span>3</span><span>)
</span><span>28</span> <span>GO</span>

Copy after login

View Code

下面这个证明代码在《SQLSERVER企业级平台管理实践》里找的

SQLSERVER聚集索引与非聚集索引的再次研究（上）

<span> 1</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span>
<span> 2</span> <span>GO</span>
<span> 3</span> <span>SET</span> <span>TRANSACTION</span> <span>ISOLATION</span> <span>LEVEL</span> <span>REPEATABLE</span> <span>READ</span>
<span> 4</span> <span>GO</span>
<span> 5</span> <span>--</span><span>以下查询使用了聚集索引查找 ctrl+l</span>
<span> 6</span> <span>BEGIN</span> <span>TRAN</span>
<span> 7</span> <span>SELECT</span> GroupName <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department2</span><span>]</span>  <span>WHERE</span> DepartmentID <span>IN</span>(<span>32641</span>,<span>361</span>,<span>32281</span><span>) 
</span><span> 8</span> 
<span> 9</span> <span>--</span><span>COMMIT TRAN</span>
<span>10</span> 
<span>11</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span> <span>--</span><span>要查询申请锁的数据库</span>
<span>12</span> <span>GO</span>
<span>13</span> <span>SELECT</span>
<span>14</span> <span>[</span><span>request_session_id</span><span>]</span><span>,
</span><span>15</span> c.<span>[</span><span>program_name</span><span>]</span><span>,
</span><span>16</span> <span>DB_NAME</span>(c.<span>[</span><span>dbid</span><span>]</span>) <span>AS</span><span> dbname,
</span><span>17</span> <span>[</span><span>resource_type</span><span>]</span><span>,
</span><span>18</span> <span>[</span><span>request_status</span><span>]</span><span>,
</span><span>19</span> <span>[</span><span>request_mode</span><span>]</span><span>,
</span><span>20</span> <span>[</span><span>resource_description</span><span>]</span>,<span>OBJECT_NAME</span>(p.<span>[</span><span>object_id</span><span>]</span>) <span>AS</span><span> objectname,
</span><span>21</span> p.<span>[</span><span>index_id</span><span>]</span>
<span>22</span> <span>FROM</span> sys.<span>[</span><span>dm_tran_locks</span><span>]</span> <span>AS</span> a <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>partitions</span><span>]</span> <span>AS</span><span> p
</span><span>23</span> <span>ON</span> a.<span>[</span><span>resource_associated_entity_id</span><span>]</span><span>=</span>p.<span>[</span><span>hobt_id</span><span>]</span>
<span>24</span> <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>sysprocesses</span><span>]</span> <span>AS</span> c <span>ON</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span>c.<span>[</span><span>spid</span><span>]</span>
<span>25</span> <span>WHERE</span> c.<span>[</span><span>dbid</span><span>]</span><span>=</span><span>DB_ID</span>(<span>'</span><span>pratice</span><span>'</span>) <span>AND</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span><span>@@SPID</span>  <span>--</span><span>--要查询申请锁的数据库</span>
<span>26</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>request_session_id</span><span>]</span>,<span>[</span><span>resource_type</span><span>]</span>

Copy after login

View Code

SQLSERVER聚集索引与非聚集索引的再次研究（上）

（2）聚集索引扫描

先drop掉Department2表，然后重新创建Department2表

SQLSERVER聚集索引与非聚集索引的再次研究（上）

<span> 1</span> <span>--</span><span>只有聚集索引</span>
<span> 2</span> <span>CREATE</span> <span>TABLE</span><span> Department2(
</span><span> 3</span>     DepartmentID <span>int</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>NOT</span> <span>NULL</span> <span>PRIMARY</span> <span>KEY</span><span>,
</span><span> 4</span>     Name <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 5</span>     GroupName <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 6</span>     Company <span>NVARCHAR</span>(<span>300</span><span>),
</span><span> 7</span>     ModifiedDate <span>datetime</span> <span>NOT</span> <span>NULL</span>  <span>DEFAULT</span> (<span>getdate</span><span>())
</span><span> 8</span> <span>)
</span><span> 9</span> 
<span>10</span> <span>DECLARE</span> <span>@i</span> <span>INT</span>
<span>11</span> <span>SET</span> <span>@i</span><span>=</span><span>1</span>
<span>12</span> <span>WHILE</span> <span>@i</span> <span> <span>100000</span> 
<span>13</span>     <span>BEGIN</span>
<span>14</span>         <span>INSERT</span>  <span>INTO</span> Department3 ( name, <span>[</span><span>Company</span><span>]</span><span>, groupname )
</span><span>15</span>         <span>VALUES</span>  ( <span>'</span><span>销售部</span><span>'</span>, <span>'</span><span>中国你好有限公司XX分公司</span><span>'</span><span>+</span><span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span>)), <span>'</span><span>销售组</span><span>'</span><span>+</span><span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span><span>)) )
</span><span>16</span>         <span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span>
<span>17</span>     <span>END</span>
<span>18</span> 
<span>19</span> 
<span>20</span> <span>SELECT</span> <span>*</span> <span>FROM</span> Department2</span>

Copy after login

View Code

证明：

SQLSERVER聚集索引与非聚集索引的再次研究（上）

<span> 1</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span>
<span> 2</span> <span>GO</span>
<span> 3</span> <span>SET</span> <span>TRANSACTION</span> <span>ISOLATION</span> <span>LEVEL</span> <span>REPEATABLE</span> <span>READ</span>
<span> 4</span> <span>GO</span>
<span> 5</span> <span>--</span><span>以下查询使用了聚集索引查找 ctrl+l</span>
<span> 6</span> <span>BEGIN</span> <span>TRAN</span>
<span> 7</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department2</span><span>]</span>  <span>WHERE</span> <span>[</span><span>GroupName</span><span>]</span> <span>=</span><span>'</span><span>销售组83421</span><span>'</span>
<span> 8</span> 
<span> 9</span> <span>--</span><span>COMMIT TRAN</span>
<span>10</span> 
<span>11</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span> <span>--</span><span>要查询申请锁的数据库</span>
<span>12</span> <span>GO</span>
<span>13</span> <span>SELECT</span>
<span>14</span> <span>[</span><span>request_session_id</span><span>]</span><span>,
</span><span>15</span> c.<span>[</span><span>program_name</span><span>]</span><span>,
</span><span>16</span> <span>DB_NAME</span>(c.<span>[</span><span>dbid</span><span>]</span>) <span>AS</span><span> dbname,
</span><span>17</span> <span>[</span><span>resource_type</span><span>]</span><span>,
</span><span>18</span> <span>[</span><span>request_status</span><span>]</span><span>,
</span><span>19</span> <span>[</span><span>request_mode</span><span>]</span><span>,
</span><span>20</span> <span>[</span><span>resource_description</span><span>]</span>,<span>OBJECT_NAME</span>(p.<span>[</span><span>object_id</span><span>]</span>) <span>AS</span><span> objectname,
</span><span>21</span> p.<span>[</span><span>index_id</span><span>]</span>
<span>22</span> <span>FROM</span> sys.<span>[</span><span>dm_tran_locks</span><span>]</span> <span>AS</span> a <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>partitions</span><span>]</span> <span>AS</span><span> p
</span><span>23</span> <span>ON</span> a.<span>[</span><span>resource_associated_entity_id</span><span>]</span><span>=</span>p.<span>[</span><span>hobt_id</span><span>]</span>
<span>24</span> <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>sysprocesses</span><span>]</span> <span>AS</span> c <span>ON</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span>c.<span>[</span><span>spid</span><span>]</span>
<span>25</span> <span>WHERE</span> c.<span>[</span><span>dbid</span><span>]</span><span>=</span><span>DB_ID</span>(<span>'</span><span>pratice</span><span>'</span>) <span>AND</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span><span>@@SPID</span>  <span>--</span><span>--要查询申请锁的数据库</span>
<span>26</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>request_session_id</span><span>]</span>,<span>[</span><span>resource_type</span><span>]</span>

Copy after login

View Code

SQLSERVER聚集索引与非聚集索引的再次研究（上）

上图“以下查询使用了聚集索引查找”，由于本人写SQL代码的时候没有修改上面注释，大家可以不用理会

为什麽会有一个键锁，那么多的页锁，在徐海蔚老师的《SQLSERVER企业级平台管理实践》的书本里第361页说到

因为在有聚集索引的表格上，数据是直接存放在索引的最底层（叶子节点），所以要扫描整个表格里的数据，就要把整个聚集索引

扫描一遍。在这里，聚集索引扫描就相当于一个表扫描。所要用的时间和资源与表扫描没有什么差别

SQLSERVER聚集索引与非聚集索引的再次研究（上）

再看一下聚集索引查找的流程

SQLSERVER首先把每个数据页的头一条记录里的DepartmentID的值加上一定范围值hash出一个key值，然后放在KeyHashValue列里

当我要找DepartmentID为110的那条记录里的GroupName和Company的值的时候，首先SQLSERVER根据where DepartmentID=110

将110加上一个范围值hash出一个值，这个值就是KeyHashValue的值，找到KeyHashValue=(f000ff86397c)的那条记录

然后到数据页13791里找出DepartmentID为110的那条记录里的GroupName和Company的值

因为[GroupName]列不是索引列，所以根本找不到KeyHashValue值，所以这里只能使用扫描所有数据页的方法来找出记录，除非找到那条记录

不然SQLSERVER不会停止扫描数据页，所以才看到上图有那么多的页面上加了页锁，SQLSERVER需要逐个数据页逐个数据页去扫描就像堆表的全表扫描那样。

那个键锁我估计是当SQLSERVER找到那条记录之后，需要在

记录的所在页面（即是索引页指向那个记录的数据页的那一行）加上一个键锁，以防止别人删除索引页的那一行记录

但是聚集索引扫描是不是一定比聚集索引查找要差呢？这个不一定，要看实际情况o(∩_∩)o

那么非聚集索引扫描是不是跟聚集索引扫描一样，所要用的时间和资源与表扫描没有什么差别呢？？？

大家可以看一下《SQLSERVER聚集索引与非聚集索引的再次研究（下）》本人做的一个小实验

实验证明了《SQLSERVER企业级平台管理实践》里第363页说到的内容

索引扫描表明SQLSERVER正在扫描一个非聚集索引。由于非聚集索引上一般只会有一小部分字段，所以这里虽然也是扫描，但是

代价会比整表扫描要小很多

SQLSERVER聚集索引与非聚集索引的再次研究（上）

------------------------------------------------华丽的分割线--------------------------------------------------------------------

这里有一个问题：没有主键但是有聚集索引，索引页的列数不一样，会多了一列，而这个列（uniquifier）的作用在下面会讲到

这里创建Department3表

SQLSERVER聚集索引与非聚集索引的再次研究（上）

<span> 1</span> <span>--</span><span>只有聚集索引</span>
<span> 2</span> <span>CREATE</span> <span>TABLE</span><span> Department3(
</span><span> 3</span>     DepartmentID <span>int</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>NOT</span> <span>NULL</span><span> ,
</span><span> 4</span>     Name <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 5</span>     GroupName <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span> 6</span>     Company <span>NVARCHAR</span>(<span>300</span><span>),
</span><span> 7</span>     ModifiedDate <span>datetime</span> <span>NOT</span> <span>NULL</span>  <span>DEFAULT</span> (<span>getdate</span><span>())
</span><span> 8</span> <span>)
</span><span> 9</span> 
<span>10</span> <span>CREATE</span> <span>CLUSTERED</span> <span>INDEX</span> CL_DepartmentID <span>ON</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department3</span><span>]</span>(<span>[</span><span>DepartmentID</span><span>]</span><span>)
</span><span>11</span> 
<span>12</span> <span>DECLARE</span> <span>@i</span> <span>INT</span>
<span>13</span> <span>SET</span> <span>@i</span><span>=</span><span>1</span>
<span>14</span> <span>WHILE</span> <span>@i</span> <span> <span>100000</span> 
<span>15</span>     <span>BEGIN</span>
<span>16</span>         <span>INSERT</span>  <span>INTO</span> Department3 ( name, <span>[</span><span>Company</span><span>]</span><span>, groupname )
</span><span>17</span>         <span>VALUES</span>  ( <span>'</span><span>销售部</span><span>'</span>, <span>'</span><span>中国你好有限公司XX分公司</span><span>'</span><span>+</span><span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span>)), <span>'</span><span>销售组</span><span>'</span><span>+</span><span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span><span>)) )
</span><span>18</span>         <span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span>
<span>19</span>     <span>END</span>
<span>20</span> 
<span>21</span> 
<span>22</span> <span>SELECT</span> <span>*</span> <span>FROM</span><span> Department3
</span><span>23</span> 
<span>24</span> <span>--</span><span>TRUNCATE TABLE [dbo].[DBCCResult]</span>
<span>25</span> 
<span>26</span> <span>INSERT</span> <span>INTO</span> DBCCResult <span>EXEC</span> (<span>'</span><span>DBCC IND(pratice,Department3,-1) </span><span>'</span><span>)
</span><span>27</span> 
<span>28</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>DBCCResult</span><span>]</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>PageType</span><span>]</span> <span>DESC</span> 
<span>29</span> 
<span>30</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>13861</span>,<span>3</span><span>)
</span><span>31</span> <span>GO</span></span>

Copy after login

View Code

SQLSERVER聚集索引与非聚集索引的再次研究（上）

可以看到只有聚集索引没有主键的表会比主键表多了一列uniquifier列，这个列的作用会在创建Department5表的时候讲到

-----------------------------------------------华丽的分割线-------------------------------------------------------

下面说一下，复合主键或者聚集索引建立在多个字段上，KeyHashValue只会根据第一个字段生成hash key

当你查询的时候where 后面的字段不包含创建聚集索引时的第一个字段或者复合主键的第一个字段就会聚集索引扫描而不是聚集索引查找

创建Department4表

SQLSERVER聚集索引与非聚集索引的再次研究（上）

<span> 1</span> <span>--</span><span>只有聚集索引</span>
<span> 2</span> <span>CREATE</span> <span>TABLE</span> Department4  <span>--</span><span>包含复合主键DepartmentID 和Name</span>
<span> 3</span> <span>(
</span><span> 4</span>   DepartmentID <span>INT</span> <span>IDENTITY</span>(<span>1</span>, <span>1</span>) <span>NOT</span> <span>NULL</span><span> ,
</span><span> 5</span>   Name <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span> ,
</span><span> 6</span>   GroupName <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span> ,
</span><span> 7</span>   Company <span>NVARCHAR</span>(<span>300</span><span>) ,
</span><span> 8</span>   ModifiedDate <span>DATETIME</span> <span>NOT</span> <span>NULL</span> <span>DEFAULT</span> ( <span>GETDATE</span><span>() ) ,
</span><span> 9</span>   <span>CONSTRAINT</span> <span>[</span><span>PK_Department4_1</span><span>]</span> <span>PRIMARY</span> <span>KEY</span> <span>CLUSTERED</span>
<span>10</span>     ( DepartmentID <span>ASC</span><span>,
</span><span>11</span>       Name <span>ASC</span><span> )
</span><span>12</span>     <span>WITH</span> ( PAD_INDEX <span>=</span> <span>OFF</span>, STATISTICS_NORECOMPUTE <span>=</span> <span>OFF</span>, IGNORE_DUP_KEY <span>=</span> <span>OFF</span><span>,
</span><span>13</span>            ALLOW_ROW_LOCKS <span>=</span> <span>ON</span>, ALLOW_PAGE_LOCKS <span>=</span> <span>ON</span> ) <span>ON</span> <span>[</span><span>PRIMARY</span><span>]</span>
<span>14</span> ) <span>ON</span> <span>[</span><span>PRIMARY</span><span>]</span>
<span>15</span> 
<span>16</span> 
<span>17</span> <span>DECLARE</span> <span>@i</span> <span>INT</span>
<span>18</span> <span>SET</span> <span>@i</span><span>=</span><span>1</span>
<span>19</span> <span>WHILE</span> <span>@i</span> <span> <span>100000</span> 
<span>20</span>     <span>BEGIN</span>
<span>21</span>         <span>INSERT</span>  <span>INTO</span> Department4 ( name, <span>[</span><span>Company</span><span>]</span><span>, groupname )
</span><span>22</span>         <span>VALUES</span>  ( <span>'</span><span>销售部</span><span>'</span><span>+</span><span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span>)), <span>'</span><span>中国你好有限公司XX分公司</span><span>'</span>, <span>'</span><span>销售组</span><span>'</span><span> )
</span><span>23</span>         <span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span>
<span>24</span>     <span>END</span>
<span>25</span> 
<span>26</span> 
<span>27</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department4</span><span>]</span>
<span>28</span> 
<span>29</span> 
<span>30</span> 
<span>31</span> <span>--</span><span>TRUNCATE TABLE [dbo].[DBCCResult]</span>
<span>32</span> <span>INSERT</span> <span>INTO</span> DBCCResult <span>EXEC</span> (<span>'</span><span>DBCC IND(pratice,Department4,-1) </span><span>'</span><span>)
</span><span>33</span> 
<span>34</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>DBCCResult</span><span>]</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>PageType</span><span>]</span> <span>DESC</span> 
<span>35</span> 
<span>36</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>7102</span>,<span>3</span><span>)
</span><span>37</span> <span>GO</span></span>

Copy after login

View Code

SQLSERVER聚集索引与非聚集索引的再次研究（上）

<span>1</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department4</span><span>]</span> <span>WHERE</span> name<span>=</span><span>'</span><span>销售部6</span><span>'</span>  <span>--</span><span>聚集索引扫描  因为name不是复合主键中的第一个字段</span>
<span>2</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department4</span><span>]</span> <span>WHERE</span> name<span>=</span><span>'</span><span>销售部241</span><span>'</span> <span>AND</span> <span>[</span><span>DepartmentID</span><span>]</span><span>=</span><span>241</span>  <span>--</span><span>聚集索引查找</span>
<span>3</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department4</span><span>]</span> <span>WHERE</span> <span>[</span><span>DepartmentID</span><span>]</span><span>=</span><span>241</span>   <span>--</span><span>聚集索引查找</span>

Copy after login

SQLSERVER聚集索引与非聚集索引的再次研究（上）

在建立聚集索引的时候在多个字段上建立聚集索引是没有任何意义的

因为聚集索引查找是根据建立索引的第一个字段来查找，索引扫描的时候会到数据页里扫描，而聚集索引的每一行只是一个数据页的范围值从而不能直接定位到要找的那条记录

所以只需要在数据表的一个字段上建立聚集索引就可以了，而究竟要在哪一个字段上建立聚集索引大家一定好好斟酌，本人建议那一个字段在order by中经常要排序的

因为数据页都已经按照聚集索引的第一个字段排好序的了

而不像非聚集索引的索引页跟数据表的记录一一对应，扫描的时候扫描索引页的每一行

大家可以对比一下聚集索引和非聚集索引页的结构

聚集索引页的结构

SQLSERVER聚集索引与非聚集索引的再次研究（上）

非聚集索引页的结构

SQLSERVER聚集索引与非聚集索引的再次研究（上）

非聚集索引页面的结构会在SQLSERVER聚集索引与非聚集索引的再次研究（下）里讲到

---------------------------------------------------------华丽的分割线-----------------------------------------------------

由于主键不允许重复值，那么就在表上创建一个不唯一的聚集索引，有人说在重复值很多的列上建立聚集索引没有意义

创建Department5表在Company字段上建立聚集索引，Company字段的值全部都是"中国你好有限公司XX分公司"

SQLSERVER聚集索引与非聚集索引的再次研究（上）

<span> 1</span> <span>--</span><span>只有聚集索引</span>
<span> 2</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span>
<span> 3</span> <span>GO</span>
<span> 4</span> <span>CREATE</span> <span>TABLE</span><span> Department5
</span><span> 5</span> <span>(
</span><span> 6</span>   DepartmentID <span>INT</span> <span>IDENTITY</span>(<span>1</span>, <span>1</span>) <span>NOT</span> <span>NULL</span><span> ,
</span><span> 7</span>   Name <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span> ,
</span><span> 8</span>   GroupName <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span> ,
</span><span> 9</span>   Company <span>NVARCHAR</span>(<span>300</span><span>) ,
</span><span>10</span>   ModifiedDate <span>DATETIME</span> <span>NOT</span> <span>NULL</span>  <span>DEFAULT</span> ( <span>GETDATE</span><span>() ) 
</span><span>11</span> <span>)
</span><span>12</span> 
<span>13</span> <span>CREATE</span> <span>CLUSTERED</span> <span>INDEX</span> CL_Company <span>ON</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department5</span><span>]</span>(<span>[</span><span>Company</span><span>]</span> <span>ASC</span><span>)
</span><span>14</span> 
<span>15</span> <span>--</span><span>DROP TABLE [dbo].[Department5]</span>
<span>16</span> 
<span>17</span> <span>DECLARE</span> <span>@i</span> <span>INT</span>
<span>18</span> <span>SET</span> <span>@i</span><span>=</span><span>1</span>
<span>19</span> <span>WHILE</span> <span>@i</span> <span> <span>10000</span>
<span>20</span>     <span>BEGIN</span>
<span>21</span>         <span>INSERT</span>  <span>INTO</span> Department5 ( name, <span>[</span><span>Company</span><span>]</span><span>, groupname )
</span><span>22</span>         <span>VALUES</span>  ( <span>'</span><span>销售部</span><span>'</span><span>+</span><span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span>)), <span>'</span><span>中国你好有限公司XX分公司</span><span>'</span>, <span>'</span><span>销售组</span><span>'</span><span> )
</span><span>23</span>         <span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span>
<span>24</span>     <span>END</span></span>

Copy after login

View Code

SQLSERVER聚集索引与非聚集索引的再次研究（上）

<span>1</span> <span>--</span><span>TRUNCATE TABLE [dbo].[DBCCResult]</span>
<span>2</span> <span>INSERT</span> <span>INTO</span> DBCCResult <span>EXEC</span> (<span>'</span><span>DBCC IND(pratice,Department5,-1) </span><span>'</span><span>)
</span><span>3</span> 
<span>4</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>DBCCResult</span><span>]</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>PageType</span><span>]</span> <span>DESC</span> 
<span>5</span> 
<span>6</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>14516</span>,<span>3</span><span>)
</span><span>7</span> <span>GO</span>

Copy after login

View Code

SQLSERVER聚集索引与非聚集索引的再次研究（上）

在Department3表的时候讲到列（uniquifier），为什麽有主键的表没有这个列，而聚集索引的表有这个列，原因在于

主键列不能有重复值，必须是唯一的，而聚集索引允许有重复值，所以聚集索引需要增加列（uniquifier）来区分重复值

而且可以看到这里uniquifier列是没有规律的，不像Department表每隔120行记录在索引页里标记一行

看一下执行计划和执行结果

<span> 1</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>
<span> 2</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department5</span><span>]</span> <span>WHERE</span> <span>[</span><span>Company</span><span>]</span><span>=</span><span>'</span><span>中国你好有限公司XX分公司</span><span>'</span> <span>AND</span> <span>[</span><span>DepartmentID</span><span>]</span><span>=</span><span>241</span> 
<span> 3</span> 
<span> 4</span> <span>SQL Server 分析和编译时间: 
</span><span> 5</span>    CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。
</span><span> 6</span> 
<span> 7</span> (<span>1</span><span> 行受影响)
</span><span> 8</span> 
<span> 9</span> <span>SQL Server 执行时间:
</span><span>10</span>    CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span> 毫秒。

Copy after login

SQLSERVER聚集索引与非聚集索引的再次研究（上）

<span>1</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>
<span>2</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department5</span><span>]</span> <span>WHERE</span> name<span>=</span><span>'</span><span>销售部106</span><span>'</span> <span>AND</span> <span>[</span><span>DepartmentID</span><span>]</span><span>=</span><span>106</span>  <span>--</span><span>聚集索引扫描</span>
<span>3</span> <span>SQL Server 执行时间:
</span><span>4</span>    CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span> 毫秒。

Copy after login

SQLSERVER聚集索引与非聚集索引的再次研究（上）

至于应不应该在重复值很多的列上建立聚集索引我这里也不敢妄下判断，因为实际环境和这里的测试环境不一样

在MSDN中的解释：http://msdn.microsoft.com/zh-cn/library/ms177484(v=SQL.105).aspx

如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见

还有一个，看一下叶子节点中的数据页，在每个数据页的每行记录中都有

Slot 101 Column 0 Offset 0x1d Length 4

UNIQUIFIER = 206

因为需要标记索引列中的唯一，所以需要在每行记录中增加一列UNIQUIFIER ，但是这一列在select * 表中数据的时候是select不出来的

还有人说UNIQUIFIER 是一个可变长度的字段，但是Length 4已经说明了是一个占用4字节的字段

SQLSERVER聚集索引与非聚集索引的再次研究（上）

<span>   1</span> PAGE: (<span>1</span>:<span>14517</span><span>)
</span><span>   2</span> 
<span>   3</span> 
<span>   4</span> <span>BUFFER:
</span><span>   5</span> 
<span>   6</span> 
<span>   7</span> BUF <span>@0x03EC9D64</span>
<span>   8</span> 
<span>   9</span> bpage <span>=</span> <span>0x1A096000</span>                   bhash <span>=</span> <span>0x00000000</span>                   bpageno <span>=</span> (<span>1</span>:<span>14517</span><span>)
</span><span>  10</span> bdbid <span>=</span> <span>5</span>                            breferences <span>=</span> <span>0</span>                      bUse1 <span>=</span> <span>4722</span>
<span>  11</span> bstat <span>=</span> <span>0x3c00009</span>                    blog <span>=</span> <span>0x32159</span>                       bnext <span>=</span> <span>0x00000000</span>
<span>  12</span> 
<span>  13</span> <span>PAGE HEADER:
</span><span>  14</span> 
<span>  15</span> 
<span>  16</span> Page <span>@0x1A096000</span>
<span>  17</span> 
<span>  18</span> m_pageId <span>=</span> (<span>1</span>:<span>14517</span>)                 m_headerVersion <span>=</span> <span>1</span>                  m_type <span>=</span> <span>1</span>
<span>  19</span> m_typeFlagBits <span>=</span> <span>0x4</span>                 m_level <span>=</span> <span>0</span>                          m_flagBits <span>=</span> <span>0x200</span>
<span>  20</span> m_objId (AllocUnitId.idObj) <span>=</span> <span>317</span>    m_indexId (AllocUnitId.idInd) <span>=</span> <span>256</span>  
<span>  21</span> Metadata: AllocUnitId <span>=</span> <span>72057594058702848</span>                                 
<span>  22</span> Metadata: PartitionId <span>=</span> <span>72057594049462272</span>                                 Metadata: IndexId <span>=</span> <span>1</span>
<span>  23</span> Metadata: ObjectId <span>=</span> <span>1022626686</span>      m_prevPage <span>=</span> (<span>1</span>:<span>14514</span>)               m_nextPage <span>=</span> (<span>1</span>:<span>14518</span><span>)
</span><span>  24</span> pminlen <span>=</span> <span>16</span>                         m_slotCnt <span>=</span> <span>102</span>                      m_freeCnt <span>=</span> <span>38</span>
<span>  25</span> m_freeData <span>=</span> <span>7950</span>                    m_reservedCnt <span>=</span> <span>0</span>                    m_lsn <span>=</span> (<span>2568</span>:<span>5252</span>:<span>8</span><span>)
</span><span>  26</span> m_xactReserved <span>=</span> <span>0</span>                   m_xdesId <span>=</span> (<span>0</span>:<span>0</span>)                     m_ghostRecCnt <span>=</span> <span>0</span>
<span>  27</span> m_tornBits <span>=</span> <span>-</span><span>1007571449</span>             
<span>  28</span> 
<span>  29</span> <span>Allocation Status
</span><span>  30</span> 
<span>  31</span> GAM (<span>1</span>:<span>2</span>) <span>=</span> ALLOCATED                SGAM (<span>1</span>:<span>3</span>) <span>=</span> <span>NOT</span><span> ALLOCATED           
</span><span>  32</span> PFS (<span>1</span>:<span>8088</span>) <span>=</span> <span>0x60</span> MIXED_EXT ALLOCATED   0_PCT_FULL                      DIFF (<span>1</span>:<span>6</span>) <span>=</span> <span>NOT</span><span> CHANGED
</span><span>  33</span> ML (<span>1</span>:<span>7</span>) <span>=</span> <span>NOT</span><span> MIN_LOGGED            
</span><span>  34</span> 

<span>  35</span> Slot <span>0</span> Offset <span>0x60</span> Length <span>77</span>
<span>  36</span> 
<span>  37</span> Record Type <span>=</span> PRIMARY_RECORD         Record Attributes <span>=</span><span>  NULL_BITMAP VARIABLE_COLUMNS
</span><span>  38</span> 
<span>  39</span> Memory <span>Dump</span> <span>@0x0A64C060</span>
<span>  40</span> 
<span>  41</span> <span>00000000</span>:   <span>30001000</span> 6a000000 d42e7c01 fea10000 †<span>0</span>...j.....<span>|</span><span>.....         
</span><span>  42</span> <span>00000010</span>:   0600c004 0021003b 0047004d <span>00690000</span><span> †.....!.;.G.M.i..         
</span><span>  43</span> <span>00000020</span>:   002d4efd 56604f7d <span>59096750</span> 966c51f8 †.<span>-</span><span>N.V`O}Y.gP.lQ.         
</span><span>  44</span> <span>00000030</span>:   <span>53580058</span><span> 0006526c 51f85300 952e55e8 †SX.X..RlQ.S...U.         
</span><span>  45</span> <span>00000040</span>:   <span>90310030</span> <span>00360000</span> 952e55c4 7e††††††††.<span>1.0</span>.<span>6</span>....U.<span>~</span>            
<span>  46</span> 
<span>  47</span> Slot <span>0</span> <span>Column</span> <span>0</span> Offset <span>0x1d</span> Length <span>4</span>
<span>  48</span> 
<span>  49</span> UNIQUIFIER <span>=</span> <span>105</span>                     
<span>  50</span> 
<span>  51</span> Slot <span>0</span> <span>Column</span> <span>1</span> Offset <span>0x21</span> Length <span>26</span>
<span>  52</span> 
<span>  53</span> Company <span>=</span><span> 中国你好有限公司XX分公司   
</span><span>  54</span> 
<span>  55</span> Slot <span>0</span> <span>Column</span> <span>2</span> Offset <span>0x4</span> Length <span>4</span>
<span>  56</span> 
<span>  57</span> DepartmentID <span>=</span> <span>106</span>                   
<span>  58</span> 
<span>  59</span> Slot <span>0</span> <span>Column</span> <span>3</span> Offset <span>0x3b</span> Length <span>12</span>
<span>  60</span> 
<span>  61</span> Name <span>=</span><span> 销售部106                     
</span><span>  62</span> 
<span>  63</span> Slot <span>0</span> <span>Column</span> <span>4</span> Offset <span>0x47</span> Length <span>6</span>
<span>  64</span> 
<span>  65</span> GroupName <span>=</span><span> 销售组                   
</span><span>  66</span> 
<span>  67</span> Slot <span>0</span> <span>Column</span> <span>5</span> Offset <span>0x8</span> Length <span>8</span>
<span>  68</span> 
<span>  69</span> ModifiedDate <span>=</span> <span>07</span> <span>17</span> <span>2013</span> <span>11</span><span>:04PM    
</span><span>  70</span> 
<span>  71</span> Slot <span>1</span> Offset <span>0xad</span> Length <span>77</span>
<span>  72</span> 
<span>  73</span> Record Type <span>=</span> PRIMARY_RECORD         Record Attributes <span>=</span><span>  NULL_BITMAP VARIABLE_COLUMNS
</span><span>  74</span> 
<span>  75</span> Memory <span>Dump</span> <span>@0x0A64C0AD</span>
<span>  76</span> 
<span>  77</span> <span>00000000</span>:   <span>30001000</span> 6b000000 d42e7c01 fea10000 †<span>0</span>...k.....<span>|</span><span>.....         
</span><span>  78</span> <span>00000010</span><span>:   0600c004 0021003b 0047004d 006a0000 †.....!.;.G.M.j..         
</span><span>  79</span> <span>00000020</span>:   002d4efd 56604f7d <span>59096750</span> 966c51f8 †.<span>-</span><span>N.V`O}Y.gP.lQ.         
</span><span>  80</span> <span>00000030</span>:   <span>53580058</span><span> 0006526c 51f85300 952e55e8 †SX.X..RlQ.S...U.         
</span><span>  81</span> <span>00000040</span>:   <span>90310030</span> <span>00370000</span> 952e55c4 7e††††††††.<span>1.0</span>.<span>7</span>....U.<span>~</span>            
<span>  82</span> 
<span>  83</span> Slot <span>1</span> <span>Column</span> <span>0</span> Offset <span>0x1d</span> Length <span>4</span>
<span>  84</span> 
<span>  85</span> UNIQUIFIER <span>=</span> <span>106</span>                     
<span>  86</span> 
<span>  87</span> Slot <span>1</span> <span>Column</span> <span>1</span> Offset <span>0x21</span> Length <span>26</span>
<span>  88</span> 
<span>  89</span> Company <span>=</span><span> 中国你好有限公司XX分公司   
</span><span>  90</span> 
<span>  91</span> Slot <span>1</span> <span>Column</span> <span>2</span> Offset <span>0x4</span> Length <span>4</span>
<span>  92</span> 
<span>  93</span> DepartmentID <span>=</span> <span>107</span>                   
<span>  94</span> 
<span>  95</span> Slot <span>1</span> <span>Column</span> <span>3</span> Offset <span>0x3b</span> Length <span>12</span>
<span>  96</span> 
<span>  97</span> Name <span>=</span><span> 销售部107                     
</span><span>  98</span> 
<span>  99</span> Slot <span>1</span> <span>Column</span> <span>4</span> Offset <span>0x47</span> Length <span>6</span>
<span> 100</span> 
<span> 101</span> GroupName <span>=</span><span> 销售组                   
</span><span> 102</span> 
<span> 103</span> Slot <span>1</span> <span>Column</span> <span>5</span> Offset <span>0x8</span> Length <span>8</span>
<span> 104</span> 
<span> 105</span> ModifiedDate <span>=</span> <span>07</span> <span>17</span> <span>2013</span> <span>11</span><span>:04PM    
</span><span> 106</span> 
<span> 107</span> Slot <span>2</span> Offset <span>0xfa</span> Length <span>77</span>
<span> 108</span> 
<span> 109</span> Record Type <span>=</span> PRIMARY_RECORD         Record Attributes <span>=</span><span>  NULL_BITMAP VARIABLE_COLUMNS
</span><span> 110</span> 
<span> 111</span> Memory <span>Dump</span> <span>@0x0A64C0FA</span>
<span> 112</span> 
<span> 113</span> <span>00000000</span>:   <span>30001000</span> 6c000000 d42e7c01 fea10000 †<span>0</span>...l.....<span>|</span><span>.....         
</span><span> 114</span> <span>00000010</span><span>:   0600c004 0021003b 0047004d 006b0000 †.....!.;.G.M.k..         
</span><span> 115</span> <span>00000020</span>:   002d4efd 56604f7d <span>59096750</span> 966c51f8 †.<span>-</span><span>N.V`O}Y.gP.lQ.         
</span><span> 116</span> <span>00000030</span>:   <span>53580058</span><span> 0006526c 51f85300 952e55e8 †SX.X..RlQ.S...U.         
</span><span> 117</span> <span>00000040</span>:   <span>90310030</span> <span>00380000</span> 952e55c4 7e††††††††.<span>1.0</span>.<span>8</span>....U.<span>~</span>            
<span> 118</span> 
<span> 119</span> Slot <span>2</span> <span>Column</span> <span>0</span> Offset <span>0x1d</span> Length <span>4</span>
<span> 120</span> 
<span> 121</span> UNIQUIFIER <span>=</span> <span>107</span>                     
<span> 122</span> 
<span> 123</span> Slot <span>2</span> <span>Column</span> <span>1</span> Offset <span>0x21</span> Length <span>26</span>
<span> 124</span> 
<span> 125</span> Company <span>=</span><span> 中国你好有限公司XX分公司   
</span><span> 126</span> 
<span> 127</span> Slot <span>2</span> <span>Column</span> <span>2</span> Offset <span>0x4</span> Length <span>4</span>
<span> 128</span> 
<span> 129</span> DepartmentID <span>=</span> <span>108</span>                   
<span> 130</span> 
<span> 131</span> Slot <span>2</span> <span>Column</span> <span>3</span> Offset <span>0x3b</span> Length <span>12</span>
<span> 132</span> 
<span> 133</span> Name <span>=</span><span> 销售部108                     
</span><span> 134</span> 
<span> 135</span> Slot <span>2</span> <span>Column</span> <span>4</span> Offset <span>0x47</span> Length <span>6</span>
<span> 136</span> 
<span> 137</span> GroupName <span>=</span><span> 销售组                   
</span><span> 138</span> 
<span> 139</span> Slot <span>2</span> <span>Column</span> <span>5</span> Offset <span>0x8</span> Length <span>8</span>
<span> 140</span> 
<span> 141</span> ModifiedDate <span>=</span> <span>07</span> <span>17</span> <span>2013</span> <span>11</span><span>:04PM    
</span><span> 142</span> 
<span> 143</span> Slot <span>3</span> Offset <span>0x147</span> Length <span>77</span>
<span> 144</span> 
<span> 145</span> Record Type <span>=</span> PRIMARY_RECORD         Record Attributes <span>=</span><span>  NULL_BITMAP VARIABLE_COLUMNS
</span><span> 146</span> 
<span> 147</span> Memory <span>Dump</span> <span>@0x0A64C147</span>
<span> 148</span> 
<span> 149</span> <span>00000000</span>:   <span>30001000</span> 6d000000 d42e7c01 fea10000 †<span>0</span>...m.....<span>|</span><span>.....         
</span><span> 150</span> <span>00000010</span><span>:   0600c004 0021003b 0047004d 006c0000 †.....!.;.G.M.l..         
</span><span> 151</span> <span>00000020</span>:   002d4efd 56604f7d <span>59096750</span> 966c51f8 †.<span>-</span><span>N.V`O}Y.gP.lQ.         
</span><span> 152</span> <span>00000030</span>:   <span>53580058</span><span> 0006526c 51f85300 952e55e8 †SX.X..RlQ.S...U.         
</span><span> 153</span> <span>00000040</span>:   <span>90310030</span> <span>00390000</span> 952e55c4 7e††††††††.<span>1.0</span>.<span>9</span>....U.<span>~</span>            
<span> 154</span> 
<span> 155</span> Slot <span>3</span> <span>Column</span> <span>0</span> Offset <span>0x1d</span> Length <span>4</span>
<span> 156</span> 
<span> 157</span> UNIQUIFIER <span>=</span> <span>108</span>                     
<span> 158</span> 
<span> 159</span> Slot <span>3</span> <span>Column</span> <span>1</span> Offset <span>0x21</span> Length <span>26</span>
<span> 160</span> 
<span> 161</span> Company <span>=</span><span> 中国你好有限公司XX分公司   
</span><span> 162</span> 
<span> 163</span> Slot <span>3</span> <span>Column</span> <span>2</span> Offset <span>0x4</span> Length <span>4</span>
<span> 164</span> 
<span> 165</span> DepartmentID <span>=</span> <span>109</span>                   
<span> 166</span> 
<span> 167</span> Slot <span>3</span> <span>Column</span> <span>3</span> Offset <span>0x3b</span> Length <span>12</span>
<span> 168</span> 
<span> 169</span> Name <span>=</span><span> 销售部109                     
</span><span> 170</span> 
<span> 171</span> Slot <span>3</span> <span>Column</span> <span>4</span> Offset <span>0x47</span> Length <span>6</span>
<span> 172</span> 
<span> 173</span> GroupName <span>=</span><span> 销售组                   
</span><span> 174</span> 
<span> 175</span> Slot <span>3</span> <span>Column</span> <span>5</span> Offset <span>0x8</span> Length <span>8</span>
<span> 176</span> 
<span> 177</span> ModifiedDate <span>=</span> <span>07</span> <span>17</span> <span>2013</span> <span>11</span><span>:04PM    
</span><span> 178</span> 
<span> 179</span> Slot <span>4</span> Offset <span>0x194</span> Length <span>77</span>
<span> 180</span> 
<span> 181</span> Record Typ

Copy after login