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

《Microsoft Sql server 2008 Interna》读书笔记订阅地址：

http://www.cnblogs.com/downmoon/category/230397.html/rss

 

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

 

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

 

上文简单介绍了 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.

除了上述限制外，还有：

■一个稀疏列不能有一个默认值
■A sparse column　cannot bd bound to a rule
■尽管一个计算列能被引用作稀疏列，但一个计算列不能被标记为稀疏列。
■ 一个稀疏列不能被作为一个聚集索引或一个惟一主键列索引的一部分。被引用作为持久或非持久计算列可以作为聚集 键的一部分。
■ 一个稀疏列不能被用作一个聚集索引或Heap的分区键，稀疏列可以作为一个非聚集索引的分区 键。

 ■ 列集和稀疏列操作

 一行中包含非常多列时，我们将不得不使用select *。　SQL Server　２００８提供了一个叫列集(column_set)的处理机制。这是一种非典型的XML表现形式，它将表中的多个列作为一个结构输出，有点像非持久化的计算列，因为它并没有物理存储在表中。这个版本中,column_set包含表中所有的稀疏列。未来版本中允许我们定义其他的column_set变量。

一个表仅仅能拥有一个定义的列集，一旦表中定义了列集，select *将不再返回单个的稀疏列，而是返回一个XML碎片，这个碎片包含所有稀疏列的所有非null值。我们看一个例子：

 USE testdb;
GO
IF EXISTS (SELECT * FROM sys.tables
            WHERE name = 'lots_of_sparse_columns')
        DROP TABLE lots_of_sparse_columns;
GO
CREATE TABLE lots_of_sparse_columns
(ID int IDENTITY,
 col1 int SPARSE,
 col2 int SPARSE,
 col3 int SPARSE,
 col4 int SPARSE,
 col5 int SPARSE,
 col6 int SPARSE,
 col7 int SPARSE,
 col8 int SPARSE,
 col9 int SPARSE,
 col10 int SPARSE,
 col11 int SPARSE,
 col12 int SPARSE,
 col13 int SPARSE,
 col14 int SPARSE,
 col15 int SPARSE,
 col16 int SPARSE,
 col17 int SPARSE,
 col18 int SPARSE,
 col19 int SPARSE,
 col20 int SPARSE,
 col21 int SPARSE,
 col22 int SPARSE,
 col23 int SPARSE,
 col24 int SPARSE,
 col25 int SPARSE,
 sparse_column_set XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);
 GO
--可以插入稀疏列：
INSERT INTO lots_of_sparse_columns (col4, col7, col12)  SELECT 4,6,11;
--也可以直接插入列集
INSERT INTO lots_of_sparse_columns (sparse_column_set)        SELECT '<col8>42</col8><col17>0</col17><col22>30000</col22>';

此时，可以用select *.col1等查询：

SELECT ID, col10, col15, col20
    FROM lots_of_sparse_columns;
SELECT *, col11
    FROM lots_of_sparse_columns;

  使用稀疏列的使用，请关注以下几点：

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

 关于稀疏列的使用，这里有一篇权威的文章，http://msdn.microsoft.com/en-us/library/cc280604.aspx

 关于列集的官方使用，请看：http://msdn.microsoft.com/zh-cn/library/cc280521.aspx

下文我们看看稀疏列的物理存储。