《Microsoft Sql server 2008 Internals》读书笔记--第七章Special Storage(1)

《Microsoft Sql server 2008 Internals》索引目录：

《Microsoft Sql server 2008 Internals》读书笔记--目录索引

第五章《Table》和第六章《Indexes:Internals and Management》我们主要讨论了“规则行”在表和索引中的存储机制，第五章中我们了解到规则行是以“固定变量”的格式存储，SQL Server提供了另外一种格式存储数据，即“列描述”(Column Descriptor)。它能存储一些特殊的值（包括固定变量和不能适应常规8-KB大小页面的CD格式的值），在这章中。我们将讨论一些超过正常数据行 大小限制的数据，它们被存储作为"行溢出"或Large Object(LOB)数据。此外，SQL Server 2008还提供了一种常规列的复杂存储列，即稀疏列(Sparse Columns),和新的CD格式（compressed data）。另外，还将讨论文件流访问，即SQL Server访问操作系统的文件。最后将讨论SQL Server分离数据到分区的操作。

 ■Large Object Storage

根据第五章的知识，我们了解到8k＝8192字节中，只有8060字节是用于存储数据，如果一个表的行长度超过8060，将会收到一条提示：

USE testdb; go CREATE TABLE dbo.bigrows_fixed ( a char(3000), b char(3000), c char(2000), d char(60) ) ; --Msg 1701, Level 16, State 1, Line 2 --Creating or altering table 'bigrows_fixed' failed because the minimum row size would be 8067, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes. ---- The following code creates a table with rows that have a maximum defined length that is much longe

你可以看到8067中有7个字节用于存储行自身的开销。其实还有两个字节用于存储行偏移，但没有包含在这7个字节，详见前文第五章部分。

  ■限制长度的大对象数据(行溢出数据Row-Overflow Data）

 超过8060字节的行限制的一个方法是使用可变长度列。因为SQL Server 2005/2008会使用“行溢出”页面来存储这些列，只要固定长度列不超过限制即可。可变长度列包括 varbonary,varchar,nvarchar,sqlvariant等，还可以是在CLR中用户定义的数据类型。

如下语句：

CREATE TABLE dbo.bigrows (a varchar(3000), b varchar(3000), c varchar(3000), d varchar(3000) );

事实上。这个语句在SQL Server 7.0出错而不会被执行，SQL Server 2000中会执行，但会收到一个警告，超过8060的行可能会被截断。而在SQL Server 2005/2008中，你不但可以创建表，而且可以插入超过8060字节的数据。如下：

 INSERT INTO dbo.bigrows SELECT REPLICATE('e', 2100), REPLICATE('f', 2100), REPLICATE('g', 2100), REPLICATE('h', 2100);

 我们来确认一下SQL Server是如何存储行溢出数据的：

 SELECT object_name(object_id) AS name, partition_id, partition_number AS pnum, rows, allocation_unit_id AS au_id, type_desc as page_type_desc, total_pages AS pages FROM sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE object_id=object_id('dbo.bigrows'); --name partition_id pnum rows au_id page_type_desc pages --bigrows 72057594043432960 1 1 72057594045005824 IN_ROW_DATA 2 --bigrows 72057594043432960 1 1 72057594045071360 ROW_OVERFLOW_DATA 2
结果中可以看出，这一行中有两个页用来存放规则“行内”数据，两个页来存放“行溢出”数据，此外，我们还可以使用DBCC IND命令来查看更详细的四个页的存储，
注意：当表开始增长时，这个命令将无法应付成千上万的页，第六章提供了一个sp_tablepages脚本，可以很方便捕获DBCC的输出。

 INSERT INTO sp_tablepages EXEC ('DBCC IND (testdb, bigrows, -1)') SELECT PageFID, PagePID, ObjectID, PartitionID, IAM_chain_type, PageType FROM sp_tablepages; 

 

 

 在上图中，e(对应ascii码为65)与g（67)之间对应的部分前16个字节存储了Row-Overflow的指针,如下：

字节数	十六进制值	十进制值	Meaning
0	0x02	2	1=LOB;2=Overflow
1-2	0x0000	0	B树等级，对OverFlow始终为0
3	0x00	0	未使用
4-7	0x00000163	?	序列号，在优化并发时使用
8-11	0x004ae100	?	时间戳
12-15	0x00000834	2100	长度

SQL Ｓerver 在一定条件下存储可变长度列在行溢出页。决定因素在于行自身的长度，即只有行自身需要超过8060字节时才会选择。表中的每一行要么全在行中，要么全在行 外。一个行如果包含多个large Variable-length列，那么它可以跨越多个行溢出页。  例如：

CREATE TABLE dbo.hugerows (a varchar(3000), b varchar(8000), c varchar(8000), d varchar(8000)); INSERT INTO dbo.hugerows SELECT REPLICATE('a', 3000), REPLICATE('b', 8000), REPLICATE('c', 8000), REPLICATE('d', 8000); --name partition_id pnum rows au_id page_type_desc pages --hugerows 72057594043498496 1 1 72057594045136896 IN_ROW_DATA 2 --hugerows 72057594043498496 1 1 72057594045202432 ROW_OVERFLOW_DATA 4

 注意：虽然SQL Server提供了大容量数据存储方式，你可以高效的使用特殊数据存储而不必重新设计你的表结构，但是，最佳的使用环境是：表的大部分数据是在行以内（即 常规数据）而偶尔需要大容量数据处理。

在某些情况 下，如果一个大的可变长度列被收缩，可能变成常规列，然而，出于效率考虑，如果仅减少几个字节，SQL Server并不会检查是否需要改变。只有改变长度超过１０００字节时，ＳＱＬ　Server才会检查是否需要移动为常规列。

看前 面的例子，我们修改行的长度：

UPDATE bigrows SET a = replicate('a', 1600);

 行总长度减为7900,减少500字节，此时，你仍然可以看到两个row-flow 页

--name partition_id pnum rows au_id page_type_desc pages --bigrows 72057594043432960 1 1 72057594045005824 IN_ROW_DATA 2 --bigrows 72057594043432960 1 1 72057594045071360 ROW_OVERFLOW_DATA 2

 再执行一次：

UPDATE bigrows SET a = 'aaaaa';

 

 注意：行溢出数据存储仅仅应用于可变长度列，即不超过8000字节的常规列，而 且，存储可变长度列在行溢出页，你还必须下列条件：

１、所有固定长度列，包括开销字节(overhaed bytes，还有24字节指针指向数据溢出页),总和加起来不超过8060字节。

２、可变长度列的实际数据必须大于２４字节。

３、 该列必须不是聚集索引键的一部分。

如果，单个列长度超过8000字节，则应使用LOB(text,image or ntext) 列或Max数据类型。

本文简单介绍了限制长度的Large Object Data存储形式，下一节将介绍不限长度的Large Object Data存储形式。

 

邀月注：本文版权由邀月和优快云共同所有，转载请注明出处。
助人等于自助!   3w@live.cn