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

yuanqiao

　　
　　《Microsoft Sql server 2008 Internals》读书笔记订阅地址：
　　http://www.iyunv.com/downmoon/category/230397.html/rss
　　《Microsoft Sql server 2008 Internals》索引目录：
　　《Microsoft Sql server 2008 Internal》读书笔记--目录索引
　　
　　第五章《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并不会检查是否需要改变。只有改变长度超过1000字节时，SQL 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存储形式。