SQL Server 2008稀疏列的使用

稀疏列是对 null 值采用优化的存储方式的普通列。稀疏列减少了 null 值的空间需求，但代价是检索非 null 值的开销增加。当至少能够节省 20% 到 40% 的空间时，才应考虑使用稀疏列。稀疏列和列集是使用 CREATE TABLE 或 ALTER TABLE 语句来定义的。

稀疏列可以与列集和筛选索引一起使用：

列集
INSERT、UPDATE 和 DELETE 语句可以按名称来引用稀疏列。但是，您也可以查看并处理表中的所有稀疏列，这些列组合为一个 XML 列，此列称为列集。有关列集的详细信息，请参阅使用列集。

筛选索引
因为稀疏列有许多 null 值行，所以尤其适用于筛选索引。稀疏列的筛选索引可以仅对已填充值的行编制索引。这会创建一个更小更有效的索引。有关详细信息，请参阅筛选索引设计准则。

稀疏列和筛选索引使应用程序（如 Windows SharePoint Services）可以有效地通过 SQL Server 2008 来存储和访问大量的用户定义属性。

 稀疏列的属性
稀疏列具有以下特征：

SQL Server 数据库引擎在列定义中使用 SPARSE 关键字来优化该列中的值的存储。因此，当表中的任意行的列值为 NULL 时，该值将不需要存储空间。

具有稀疏列的表的目录视图与典型表的目录视图相同。sys.columns 目录视图对于表中的每一列都包含一个对应的行，如果已定义列集，还会包含列集。

COLUMNS_UPDATED 函数返回一个 varbinary 值，指示在 DML 操作期间更新的所有列。COLUMNS_UPDATED 函数返回的位如下：

显式更新稀疏列后，该稀疏列的对应位将设置为 1，列集的对应位将设置为 1。

显式更新列集后，列集的对应位将设置为 1，该表中的所有稀疏列的对应位将设置为 1。

对于插入操作，所有位都将设置为 1。

有关列集的详细信息，请参阅使用列集。

下面的数据类型不能指定为 SPARSE：

geography
 text
 
geometry
 timestamp
 
image
 user-defined data types
 
ntext
 
 

 按数据类型估算的空间节省量
与未标记为 SPARSE 的相同数据相比，稀疏列在存储非 null 值时需要的存储空间更多。下表说明了每种数据类型的空间使用情况。NULL 百分比列指示数据中 NULL 值所占的百分比必须达到多少才能净节省 40% 的空间。

固定长度的数据类型

数据类型  非稀疏字节  稀疏字节  NULL 百分比 
bit
 0.125
 4.125
 98%
 
tinyint
 1
 5
 86%
 
smallint
 2
 6
 76%
 
int
 4
 8
 64%
 
bigint
 8
 12
 52%
 
real
 4
 8
 64%
 
float
 8
 12
 52%
 
smallmoney
 4
 8
 64%
 
money
 8
 12
 52%
 
smalldatetime
 4
 8
 64%
 
datetime
 8
 12
 52%
 
uniqueidentifier
 16
 20
 43%
 
date
 3
 7
 69%
 

长度依赖于精度的数据类型

数据类型  非稀疏字节  稀疏字节  NULL 百分比 
datetime2(0)
 6
 10
 57%
 
datetime2(7)
 8
 12
 52%
 
time(0)
 3
 7
 69%
 
time(7)
 5
 9
 60%
 
datetimetoffset(0)
 8
 12
 52%
 
datetimetoffset (7)
 10
 14
 49%
 
decimal/numeric(1,s)
 5
 9
 60%
 
decimal/numeric(38,s)
 17
 21
 42%
 
vardecimal(p,s)
 使用 decimal 类型作为保守的估计。
 
 
 

长度依赖于数据的数据类型

数据类型  非稀疏字节  稀疏字节  NULL 百分比 
sql_variant
 因基础数据类型而异
 
 
 
varchar 或char
 4+平均数据
 2+平均数据
 60%
 
nvarchar 或nchar
 4+平均数据
 2+平均数据
 60%
 
varbinary 或binary
 4+平均数据
 2+平均数据
 60%
 
xml
 4+平均数据
 2+平均数据
 60%
 
hierarchyId
 4+平均数据
 2+平均数据
 60%
 

 使用稀疏列的限制
稀疏列可以是任何 SQL Server 数据类型，其行为与其他任何列类似，但有以下限制：

稀疏列必须可为 null，并且不能有 ROWGUIDCOL 或 IDENTITY 属性。稀疏列可以为以下数据类型：text、ntext、image、timestamp、用户定义的数据类型、geometry 或 geography；或者具有 FILESTREAM 属性。

稀疏列不能有默认值。

稀疏列不能绑定到规则。

尽管计算列可以包含稀疏列，但计算列不能标记为 SPARSE。

稀疏列不能是聚集索引或唯一主键索引的一部分。但是，对稀疏列定义的持久化和非持久化计算列可以是聚集键的一部分。

稀疏列不能用作聚集索引或堆的分区键。但是，稀疏列可以用作非聚集索引的分区键。

稀疏列不能是用户定义的表类型的一部分，用户定义的表类型用在表变量和表值参数中。

使用稀疏列，行大小的最大值从 8,060 字节减少到 8,018 字节。

当行大小接近 4,009 字节时，添加或删除稀疏列会失败。当修改现有表以添加第一个稀疏列时，将通过在数据页上创建行的副本，然后删除原始行来修改行。当呈现现有行时，大于 4,009 字节的行无法写入页。这会导致添加列失败。
当从表中删除最后一个稀疏列时，存在同样的问题。如果有任意行超过 4,009 字节，则删除列将会失败。此限制不适用于不包含数据的新表。
若要更改每行数据超过 4,009 字节的表中的行，应创建一个新表，然后将数据转移到新表中。然后，删除原始表并重命名此新表；也可以截断原始表，修改原始表中的行，然后将数据移回到原始表中。

将非稀疏列改为稀疏列时，稀疏列将占用更多的空间来存储非 null 值。当行接近最大行大小的限制时，操作将失败。

 支持稀疏列的 SQL Server 技术
本部分介绍下列 SQL Server 技术如何支持稀疏列：

事务复制
事务复制支持稀疏列，但它不支持可以与稀疏列一起使用的列集。有关列集的详细信息，请参阅使用列集。
SPARSE 属性的复制由使用 sp_addarticle 或者使用 SQL Server Management Studio 中的“项目属性”对话框指定的架构选项来决定。SQL Server 的早期版本不支持稀疏列。如果必须将数据复制到早期版本，需指定不应复制 SPARSE 属性。
对于已发布的表，不能向表中添加任何新的稀疏列，也不能更改现有列的稀疏属性。如果需要执行此类操作，则应删除发布再重新创建。

合并复制
合并复制不支持稀疏列或列集。

更改跟踪
更改跟踪支持稀疏列和列集。当在表中更新列集时，更改跟踪将此视为对整个行的更新。不提供详细的更改跟踪，无法准确获取通过列集更新操作更新的稀疏列的集合。如果通过 DML 语句显式更新稀疏列，则这些稀疏列上的更改跟踪将像平常一样工作，并且可以准确地识别出已更改的列的集合。

变更数据捕获
变更数据捕获支持稀疏列，但不支持列集。

 示例
在本示例中，文档表包含具有 DocID 和 Title 列的列集。生产组希望所有生产文档都有一个 ProductionSpecification 列和一个 ProductionLocation 列。市场组希望所有市场文档都有一个 MarketingSurveyGroup 列。本示例中的代码创建一个使用稀疏列的表，向该表中插入两行，然后从该表中选择数据。

注意：
该表只有五列，以便易于显示和读取。如果设置了 ANSI_NULL_DFLT_ON 选项，则将稀疏列声明为可为 null 是可选的。
 

 复制代码
USE AdventureWorks
GO

CREATE TABLE DocumentStore
(DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL ) ;
GO

INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27)
GO

INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35')
GO若要从表中选择所有列，则应返回普通的结果集。

 复制代码
SELECT * FROM DocumentStore ;下面是结果集：

DocID  Title        ProductionSpecification  ProductionLocation  MarketingSurveyGroup

1      Tire Spec 1  AXZZ217                  27                  NULL

2      Survey 2142  NULL                     NULL                Men 25-35

因为生产部门对市场数据不感兴趣，所以他们希望使用一个仅返回感兴趣的列的列列表，如下面的查询所示。

 复制代码
SELECT DocID, Title, ProductionSpecification, ProductionLocation
FROM DocumentStore
WHERE ProductionSpecification IS NOT NULL ;下面是结果集：

DocID  Title        ProductionSpecification  ProductionLocation

1      Tire Spec 1  AXZZ217                  27

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16436858/viewspace-513657/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16436858/viewspace-513657/