何时应该使用稀疏列？（SQL SERVER 2008）

Question

何时应该使用稀疏列？（SQL SERVER 2008）

sqlsql-serverdatabase-designsql-server-2008

71

在学习SQL Server 2008新功能"稀疏列(SPARSE COLUMN)"的一些教程后，我发现当该列的值为0或NULL时，它不占用任何空间，但当有值时，它占用常规(非稀疏)列所占空间的4倍。

如果我的理解是正确的，那么在数据库设计时为什么要使用稀疏列呢？如果我使用了稀疏列，在什么情况下会被用到呢？

另外出于好奇，当将一列定义为稀疏列时，为什么不需要保留空间 (我的意思是说，内部实现方式是什么?)

- priyanka.sarkar

这个来自MSDN的链接展示了每种数据类型相对于空间节省的表格细节。在这方面可以是一个非常好的指针。 - RBT

这里有一篇关于稀疏列的非常好的阅读材料链接。 - RBT

5个回答

27

稀疏列中存储null不占用空间。
对于任何外部应用程序，该列的行为都是相同的。
使用过滤索引时，稀疏列与之非常相容，因为您只需要创建处理该列中非空属性的索引。
您可以在稀疏列上创建一个列集，该列集返回覆盖集中的所有非空数据的xml剪辑。列集本身就像一个列。注意：每个表只能有一个列集。
更改数据捕获和事务复制均可使用，但列集功能不能使用。

缺点

如果稀疏列中有数据，则它将比普通列多占用4个字节。例如，即使是1位（通常为0.125字节），也会占用4.125字节，而唯一标识符从16个字节增加到20个字节。
并非所有数据类型都可以是稀疏列：text、ntext、image、timestamp、用户定义的数据类型、geometry或geography或带有FILESTREAM属性的varbinray（max）不能是稀疏列。（已更正于17/5/2009，感谢Alex发现拼写错误）
计算列不能是稀疏列（虽然稀疏列可以参与另一个计算列的计算）
您无法应用规则或设置默认值。
稀疏列不能成为聚集索引的一部分。如果需要这样做，请基于稀疏列创建计算列，并在该列上创建聚集索引（这有点违背了其本身的目的）。
合并复制无法使用。
数据压缩无法使用。
访问（读取和写入）稀疏列更加昂贵，但我在这方面没有找到任何确切的数字。

参考资料

- rahul

谢谢..我已经看过那个网站了。但即使在那个网站上，也没能回答我在这里问的问题。在什么情况下，当它需要额外4倍的空间时，我应该使用稀疏列！ - priyanka.sarkar

4

你的阅读有误 - 它从未占用4倍的空间。具体来说，它是4*（4个字节，请参见脚注），而不是4x（乘以4）。唯一情况下它恰好是4倍的空间是char(4)，如果NULL存在超过64％的时间，则会节省空间。“*长度等于类型中包含的数据的平均值加上2或4个字节。”

- mbourgon

1

| datetime NULL      | datetime SPARSE NULL | datetime SPARSE NULL |
|--------------------|----------------------|----------------------|
| 20171213 (8 bytes) | 20171213 (12 bytes)  | 20171213 (12 bytes)  |
| NULL     (8 bytes) | 20171213 (12 bytes)  | 20171213 (12 bytes)  |
| 20171213 (8 bytes) | NULL      (0 bytes)  | NULL      (0 bytes)  |
| NULL     (8 bytes) | NULL      (0 bytes)  | NULL      (0 bytes)  |

你不仅在每一行失去4个字节，而且对于每个非空单元格中的每一行都会失去4个字节。

- Ian Boyd

0

来自SQL SERVER - 2008 - 稀疏列介绍 - 第2部分 by Pinal Dave：

所有的稀疏列都以一个XML列的形式存储在数据库中。让我们来看一下稀疏列的一些优点和缺点。

稀疏列的优点包括： - INSERT、UPDATE和DELETE语句可以通过名称引用稀疏列。稀疏列也可以作为一个XML列使用。 - 稀疏列可以利用过滤索引，其中数据填充在行中。 - 当数据库中存在零值或空值时，稀疏列可以节省大量的数据库空间。

稀疏列的缺点包括： - 稀疏列没有IDENTITY或ROWGUIDCOL属性。 - 不能将稀疏列应用于text、ntext、image、timestamp、geometry、geography或用户定义的数据类型。 - 稀疏列不能具有默认值、规则或计算列。 - 不能将稀疏列应用于聚集索引或唯一主键索引。稀疏列不能成为聚集索引键的一部分。 - 包含稀疏列的表的最大大小为8018字节，而不是常规的8060字节。涉及稀疏列的表操作会对性能产生影响。

- Place Holiday

4

栈溢出并不是进行长期讨论的地方。此外，您的回答相当晚，并且基本上只是对已经整理好并被接受的信息进行了概括。虽然这确实回答了问题，但社区可能会对此作出负面反应。我不会删除它。 - Tim Post

2

http://blog.sqlauthority.com/2008/07/14/sql-server-2008-introduction-to-sparse-columns-part-2/ - sam yi

我要点赞这个。将所有稀疏字段保存在一个XML字段中的解释让我更加清楚了。没有其他人提到过这一点。 - Brain2000

2

@Brain2000 可能是因为它不正确。这就是稀疏列的实际存储方式。http://improve.dk/sparse-column-storage-ndash-the-sparse-vector/。如果使用列集，它们可以作为 XML 返回给客户端。 - Martin Smith

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- BradC · Accepted Answer

稀疏列不会使用4倍的空间来存储值，而是每个非空值使用（固定的）4个额外字节。（正如您已经说明的那样，NULL不占用任何空间。）

因此，存储在位bit列中的非null值将是1位+ 4字节= 4.125字节。但是，如果其中99％为空，则仍然可以实现净节省。

存储在GUID（UniqueIdentifier）列中的非null值为16字节+ 4字节= 20字节。因此，即使只有50％为空，仍然可以实现净节省。

因此，“预期节省”强烈取决于我们讨论的列类型以及您估计的空值与非空值之比。变宽列（varchars）可能更难精确预测。

这个Books Online Page有一个表格，显示了不同数据类型需要多少百分比才能获得收益。

那么何时应该使用稀疏列？当您希望有显着百分比的行具有NULL值时。我想到的一些示例：

- 订单表中的“订单退回日期”列。您希望销售的非常小的百分比会导致退货产品。 - 地址表中的“第四地址”行。大多数邮寄地址，即使需要部门名称和“关心”，也可能不需要4个单独的行。 - 客户表中的“后缀”列。相当低的百分比的人在名字后面有“Jr.”，“III”或“Esquire”等。