正规网站开发流程,常州微信网站建设教程,二级域名网站好不好,免费建立网站SQL Server页有很多类型#xff1a;1 – 数据页. 记录堆或者聚集索引叶子级的数据2 – 索引页. 用于保存聚集索引中的中间页和根页#xff0c;或者非聚集索引的所有页3 – text mix page. A text page that holds small chunks of LOB values plus internal parts of text tr… SQL Server页有很多类型1 – 数据页. 记录堆或者聚集索引叶子级的数据2 – 索引页. 用于保存聚集索引中的中间页和根页或者非聚集索引的所有页3 – text mix page. A text page that holds small chunks of LOB values plus internal parts of text tree. These can be shared between LOB values in the same partition of an index or heap.4 – text tree page. A text page that holds large chunks of LOB values from a single column value.7 – sort page. A page that stores intermediate results during a sort operation.8 – GAM page. Holds global allocation information about extents in a GAM interval (every data file is split into 4GB chunks – the number of extents that can be represented in a bitmap on a single database page). Basically whether an extent is allocated or not. GAM Global Allocation Map. The first one is page 2 in each file. More on these in a later post.9 – SGAM page. Holds global allocation information about extents in a GAM interval. Basically whether an extent is available for allocating mixed-pages. SGAM Shared GAM. the first one is page 3 in each file. More on these in a later post.10 – IAM page. Holds allocation information about which extents within a GAM interval are allocated to an index or allocation unit, in SQL Server 2000 and 2005 respectively. IAM Index Allocation Map. More on these in a later post.11 – PFS page. Holds allocation and free space information about pages within a PFS interval (every data file is also split into approx 64MB chunks – the number of pages that can be represented in a byte-map on a single database page. PFS Page FreeSpace. The first one is page 1 in each file. More on these in a later post.13 – boot page. Holds information about the database. Theres only one of these in the database. Its page 9 in file 1.15 – file header page. Holds information about the file. Theres one per file and its page 0 in the file.16 – diff map page. Holds information about which extents in a GAM interval have changed since the last full or differential backup. The first one is page 6 in each file.17 – ML map page. Holds information about which extents in a GAM interval have changed while in bulk-logged mode since the last backup. This is what allows you to switch to bulk-logged mode for bulk-loads and index rebuilds without worrying about breaking a backup chain. The first one is page 7 in each file.PFS页 96480884 间隔8088 96页头4行头,slot0 8088 4slotlistGAM/SGAM964904798810 间隔 7988*8,96页头4行头90slot0,4行头7988slot1,10slotlistIAM页 964904798810 96页头4行头90slot0,4行头7988slot1,10slotlist 1. 数据页行存储格式数据页的基本格式数据页的基本格式信息助记符大小(Byte)状态ATagA1状态BTagB1固定长度大小Fsize2固定长度数据FdataFsize-4列数量Ncol2NULL位图(表中每列一个位1表示对应列为null)NullbitsCeil(Ncol/8)行中存储的可变长度列数VarCount2可变长度的偏移阵列VarOffset2*VarCount可变长数据VarDataVarOffset[VarCount]-(Fsize8-4 Ceil(Ncol/8)2*VarCount)实例USE db_TestEnvcreate table Index_test(id int,a varchar(10))goinsert into Index_test select 100,aaaaago 4000DBCC IND(db_TestEnv,Index_test,1)DBCC PAGE(db_TestEnv,1,45969,1)输出Slot 0, Offset 0x60, Length 20, DumpStyle BYTE Record Type PRIMARY_RECORD Record Attributes NULL_BITMAP VARIABLE_COLUMNSRecord Size 20 Memory Dump 0x613BC060 00000000: 30000800 64000000 02000001 00140061 †0...d..........a 00000010: 61616161 ††††††††††††††††††††††††††††aaaa a. 第一个字节 TagA 0x30 是由2个部分组成0x10(第4个位) 和0x20(第5个位),其中0x10表示有null列0x20表示有可变长0x40(第6个位)表示有版本标记0x80(第7个位)表示TagB是否有值。其中1-3位为行类型分别意思如下 0primary record堆上的数据页或者聚集索引的叶子页。 1forwarded record 被转发页 2forwarding record转发根存页(在行移动时会出现转发页和转发根存页如行溢出可以查看《深入解析sql server 2008》 5.7.4.1 和6.7.4.1的相关内容) 3index record聚集索引非叶子页或者非聚集索引记录 4blob recordblob记录 5ghost index record 影子索引被删除了没被清理可以使用显示事务来观察 6ghost data record影子记录被删除了没被清理可以使用显示事务来观察 7ghost version record幻想记录详细请看《深入解析 SQL Server 2008》 10.7.3.8b. 第二个字节TagB有2个取值0x00,0x01.如果是0x01说明是被转发页的幻影页。若为0x01则为TagA字节的解释其他的不需要解释了更具上面的表格就可以。2. 索引页行存储格式索引页行存储格式分为2种1.非叶子2.叶子。但是会因为是堆表上的非聚集索引还是聚集索引表上的非聚集索引有所不同。是否include对索引的存储格式没啥影响。1.堆表下实例USE db_TestEnvcreate table Index_test(id INT IDENTITY,a char(10),b VARCHAR(10))goinsert into Index_test select aaaaa,bbbgo 4000create nonclustered index ix_id_a on Index_test(id,a)1.1 叶子页基本格式基本格式信息助记符大小(Byte)行头Header1定长建值Fkey定长大小表记录Rowid(fileid:page:slote)RowID8(4数据页,2页号2槽号)索引记录包含的字段个数col2NULL位图(表中每列一个位1表示对应列为null)NullbitsCeil(可为空列数/8)行中存储的可变长度列数VarCount2可变长度的偏移阵列VarOffset2*VarCount可变长数据VarDataVarOffset[VarCount]-(Fkey12 Ceil(可为空列/8)2*VarCount)DBCC PAGE(db_TestEnv,1,41006,1)输出Slot 0, Offset 0x60, Length 26, DumpStyle BYTERecord Type INDEX_RECORD Record Attributes NULL_BITMAP Record Size 26Memory Dump 0x6128C06000000000: 16220100 00616161 61612020 202020f2 †....aaaaa . 00000010: e1000001 00200003 0000†††††††††††††††..... .... a. 第一个字节header有以下的意义0x40对于记录类型为索引记录总为00x20: 包含可变长字段0x10: 包含null位图数据 1-3bit 表示是否是索引记录 其他就不需要解释了按照表格可以轻易的得出。1.2 非叶子页基本格式信息助记符大小(Byte)行头Header1定长建值Fkey定长大小表记录Rowid(fileid:page:slote)RowID8(4数据页,2页号2槽号)下一个页所在的叶子节点(fileid:page)KeyRowid6(4数据页,2页号)索引记录包含的字段个数col2NULL位图(表中每列一个位1表示对应列为null)NullbitsCeil(可为空列数/8)行中存储的可变长度列数VarCount2可变长度的偏移阵列VarOffset2*VarCount可变长数据VarDataVarOffset[VarCount]-(Fkey18 Ceil(可为空列/8)2*VarCount)DBCC PAGE(db_TestEnv,1,41007,1)输出Slot 0, Offset 0x60, Length 32, DumpStyle BYTERecord Type INDEX_RECORD Record Attributes NULL_BITMAP Record Size 32Memory Dump 0x6095C06000000000: 16010000 00616161 61612020 202020f0 †.....aaaaa . 00000010: e1000001 0000007c 8d010001 00030000 †.......|........ 叶子非叶子没有什么大的区别就是非叶子少了键值所在的叶子节点。需要注意的是唯一索引的非叶子比较特别没有表记录Rowid。2.在聚集索引下的非聚集索引2.1叶子节点信息助记符大小(Byte)行头Header1定长建值聚集索引定长值Fkey定长大小索引记录包含的字段个数col2NULL位图(表中每列一个位1表示对应列为null)NullbitsCeil(可为空列数/8)行中存储的可变长度列数VarCount2可变长度的偏移阵列VarOffset2*VarCount可变长数据VarDataVarOffset[VarCount]-(Fkey18 Ceil(可为空列/8)2*VarCount)create table Index_test(id INT IDENTITY,a varchar(10),b VARCHAR(10),iid)goinsert into Index_test select aaaaa,bbgo 4000UPDATE dbo.Index_test SET iid id1create clustered index cix_id_a on Index_test(id,a)GOCREATE NONCLUSTERED INDEX [idx__iid] ON [dbo].[Index_test]([iid],[b])DBCC PAGE(db_TestEnv,1,164560,1)输出Slot 0, Offset 0x60, Length 25, DumpStyle BYTERecord Type INDEX_RECORD Record Attributes NULL_BITMAP VARIABLE_COLUMNSRecord Size 25 Memory Dump 0x6083C06000000000: 36214104 00204104 00050000 02001400 †6!A.. A......... 00000010: 19006262 61616161 61†††††††††††††††††..bbaaaaa这个没什么难度第一个字节 Header 和其他的都一样就是聚集索引上的非聚集索引会带上聚集索引的key在实例中21410400 为自己的iid20410400为id主键6262为key的键6161616161为聚集索引key。如果聚集索引是可重复的sql server会产生一个消除重复的数字被当成可变长存放在可变成区域2.2非叶子节点信息助记符大小(Byte)行头Header1定长建值聚集索引定长值Fkey定长大小下一个页所在的叶子节点(fileid:page)KeyRowid6(4数据页,2页号)索引记录包含的字段个数col2NULL位图(表中每列一个位1表示对应列为null)NullbitsCeil(可为空列数/8)行中存储的可变长度列数VarCount2可变长度的偏移阵列VarOffset2*VarCount可变长数据VarDataVarOffset[VarCount]-(Fkey18 Ceil(可为空列/8)2*VarCount)UPDATE dbo.Index_test SET b bRTRIM(id) 重建索引DBCC PAGE(db_TestEnv,1,177184,1)输出Slot 0, Offset 0x60, Length 32, DumpStyle BYTERecord Type INDEX_RECORD Record Attributes NULL_BITMAP VARIABLE_COLUMNSRecord Size 32 Memory Dump 0x607AC06000000000: 36020000 00010000 00e0b302 00010005 †6............... 00000010: 00000200 1b002000 62623161 61616161 †...... .bb1aaaaa 第一个字节和其他的都一样不解释其他的按表格都可以解析如果聚集索引是可重复的sql server会产生一个消除重复的数字被当成可变长存放在可变成区域3.聚集索引中在聚集索引下只有非叶子页才是索引页基本格式信息助记符大小(Byte)行头Header1一下层最小固定聚集索引建值Fkey固定聚集索引建大小一下层页号(fileid:page)KeyRowid6(4数据页,2页号)索引记录包含的字段个数col2NULL位图(表中每列一个位1表示对应列为null)NullbitsCeil(可为空列数/8)行中存储的可变长度列数VarCount2可变长度的偏移阵列VarOffset2*VarCount可变长数据VarDataVarOffset[VarCount]-(Fkey18 Ceil(可为空列/8)2*VarCount)实例create table Index_test(id INT IDENTITY,a varchar(10),b VARCHAR(10))goinsert into Index_test select aaaaa,bbgo 4000create clustered index cix_id_a on Index_test(id,a)GODBCC IND(db_TestEnv,Index_test,1)DBCC PAGE(db_TestEnv,1,51106,1)输出Slot 0, Offset 0x60, Length 23, DumpStyle BYTERecord Type INDEX_RECORD Record Attributes NULL_BITMAP VARIABLE_COLUMNSRecord Size 23 Memory Dump 0x6056C06000000000: 36010000 0097b300 00010002 00000100 †6............... 00000010: 17006161 616161††††††††††††††††††††††..aaaaa转载自https://www.cnblogs.com/Amaranthus/archive/2013/01/31/2886581.html文章经作者授权转载版权归原文作者所有图片来源于网络侵权必删
