为什么&什么时候应该使用SPARSE COLUMN？ （SQL SERVER 2008） [英] Why & When should I use SPARSE COLUMN? (SQL SERVER 2008)
问题描述
在SQL SERVER 2008的新功能SPARSE COLUMN通过一些教程之后，我发现如果列值为0或为空，那么它不会占用任何空间，但是当有一个值时，它占用空间的4倍常规（非稀疏）列保持。
After going thru some tutorials on SQL SERVER 2008's new feature SPARSE COLUMN, I have found that it doesn't take any space if the column value is 0 or null but when there is a value, it takes 4 times the space a regular(non sparse) column holds.
如果我的理解正确，那么为什么我会在数据库设计时去找？
如果我使用它，那么在什么情况下，我是这样的？
If my understanding is correct, then why I will go for that at the time of database design? And if I use that, then at what situation so I be?
出于好奇，当一列被定义为稀疏列（我的意思是说，内部实现是什么）
Also out of curiosity, how come no space get's reserve when a column is defined as sparse column(I mean to say, what is the internal implementation for that)
提前感谢
推荐答案
稀疏列不使用 4x空格来存储值，它使用（固定） 4个额外的字节非空值。 （正如你已经说过的那样，NULL需要0个空格。）
A sparse column doesn't use 4x the amount of space to store a value, it uses a (fixed) 4 extra bytes per non-null value. (As you've already stated, a NULL takes 0 space.)
-
所以一个非空值存储在一个位列将为1位+ 4字节= 4.125字节。但是，如果99％的值为NULL，那么它仍然是净储蓄。
So a non-null value stored in a bit column would be 1 bit + 4 bytes = 4.125 bytes. But if 99% of these are NULL, it is still a net savings.
存储在GUID（UniqueIdentifier）列是16字节+ 4字节= 20字节。因此，如果只有其中的50％是NULL，那么这仍然是净储蓄。
A non-null value stored in a GUID (UniqueIdentifier) column is 16 bytes + 4 bytes = 20 bytes. So if only 50% of these are NULL, that's still a net savings.
所以预期储蓄强烈地关注我们正在讨论的那个类类型，你对什么比率的估计是null而不是null。可变宽度列（varchars）可能难以准确预测。
So the "expected savings" depends strongly on what kind of column we're talking about, and your estimate of what ratio will be null vs non-null. Variable width columns (varchars) are probably a little more difficult to predict accurately.
此图书在线页面有一个表格，显示不同数据类型需要为空才能获得利益的百分比
This Books Online Page has a table showing what percentage of different data types would need to be null for you to end up with a benefit.
所以当你/ />应该使用稀疏列？当您希望有相当大的一部分行具有NULL值时。一些例子想到：
So when should you use a Sparse Column? When you expect a significant percentage of the rows to have a NULL value. Some examples that come to mind:
- 订单表中的订单退货日期列。您希望很少的销售额会导致退货。
- 地址表中的第4地址行。大多数邮寄地址，即使您需要部门名称和关心也许不需要4条单独的行。
- 后缀列客户表。百分之百的人有一个小或III或Esquire。
- A "Order Return Date" column in an order table. You would hope that a very small percent of sales would result in returned products.
- A "4th Address" line in an Address table. Most mailing addresses, even if you need a Department name and a "Care Of" probably don't need 4 separate lines.
- A "Suffix" column in a customer table. A fairly low percent of people have a "Jr." or "III" or "Esquire" after their name.
这篇关于为什么&什么时候应该使用SPARSE COLUMN？ （SQL SERVER 2008）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
