《Microsoft Sql server 2008 Internals》读书笔记--第七章Special Storage(5)-优快云博客

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

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

上文简单介绍了 filestream数据存储和修改的一些特性。下面我们来继续了解SQL Server 2008中新增的稀疏列(sparse column)，它可以看作是对Null值作了优化存储的普通列。

稀疏列削减了针对null值的空间存储。允许你有更多的列，而不是之前的1024列，只要它们中的大多数是null值。使用稀疏列的成本是存非null值的存储和取回的开销。

■稀疏列的管理

如果一个列的90%可能null,那么可以考虑使用稀疏列，这不是一个强制规定。而在SQL Server　2008中可以允许一个表有30000列，但非稀疏列不能超过1024列。（计算列不算作非稀疏列）计算列的数量取决于行数据和字节数。（在第五章中已经介绍过，一个固定长度的列通常使用整个列宽，一个可变长的Null列在列偏移数组中至少使用两字节）。稀疏列本身不占空间，一些固定的开销是允许稀疏列在行中必需的。一旦你用sparse属性定义一个列，SQL Server在行的末尾处定义一个稀疏向量，我们将在后面的节中“物理存储”中继续了解。但行的大小不超过8060字节，包括稀疏列。此外，行中所有固定长度的非null稀疏列不超过8019字节。

■ 表创建

看一个例子：

USE testdb; GO CREATE TABLE test_nosparse ( col1 int, col2 char(8000), col3 varchar(8000) ); GO INSERT INTO test_nosparse SELECT null, null, null; INSERT INTO test_nosparse SELECT 1, 'a', 'b'; GO

成功插入数据。但另一个表

CREATE TABLE test_sparse ( col1 int SPARSE, col2 char(8000) SPARSE, col3 varchar(8000) SPARSE ); GO INSERT INTO test_sparse SELECT NULL, NULL, NULL; INSERT INTO test_sparse SELECT 1, 'a', 'b'; GO --Msg 576, Level 16, State 5, Line 3 --Cannot create a row that has sparse data of size 8042 which is greater than the allowable maximum sparse data size of 8019.
为什么出错？int(４字节)+char(8000字节)+24字节对应行溢出指针，超过8019字节。
■ 表修改

可以修改一个表的非稀疏列为稀疏列,反之亦然。但是小心。困为如果你修改一个比较大的行，而该行中没有稀疏列改为稀疏列时，将会减少允许在一个页面中的数据的字节数，这可能导致出错。

看例子：

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'test_nosparse_alter') DROP TABLE test_nosparse_alter; GO CREATE TABLE test_nosparse_alter ( c1 int, c2 char(4020) , c3 char(4020) , c4 datetime ); GO INSERT INTO test_nosparse_alter SELECT NULL, NULL, NULL, NULL; INSERT INTO test_nosparse_alter SELECT 1, 1, 'b', GETDATE(); GO ALTER TABLE test_nosparse_alter ALTER COLUMN c4 datetime SPARSE; --Msg 1701, Level 16, State 1, Line 1 --Creating or altering table 'test_nosparse_alter' failed because the minimum row size would be 8075, including 23 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
除了上述限制外，还有：

■一个稀疏列不能有一个默认值
■列集中不能定义约束或默认值
■包含列集的表中不支持分发查询(distribute queries)
■列集不支持复制
<span style="" font-size:"="">■the change Data Capture feature dos not support COLUMN_sets
■列集不能做为任何索引的一部分，包括xml索引，全文索引和索引视图。列集不能被作为包含性列添加到任何索引。
■在一个筛选索引的筛选表达式或筛选声明中不能使用列集。
■当一个视图包含列集时，列集作为一个xml列出现在视图中。
■XML数据有２Ｇ限制。如果一行中所有non-null稀疏列的复合数据超过这个限制，操作错误。
■使用一个列集从表中复制所有的数据（使用select * into 或insert into select * ）并不复制单个的稀疏列。列集作为一个xml数据列被复制。