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

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

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

 

 这一节我们继续了解稀疏列的物理存储。

 ■物理存储

在某种角度上，你可以稀疏列作为存储，同时使用列集显示。如此，没有值的特殊列，它不会被列出，也不会占用空间；但有值的稀疏列比正常的null列得花费更多的空间。

用non-sparse列定义的表：

IDsc1sc2sc3sc4sc5sc6sc7sc8sc9 1 1        9 2  2  4  6    3       7   4 1    5     5    4    8  6   3      9 7    5  7   8  2      8  9   3   6   

用sparse列定义的表：

IDsparse columns1(sc1,sc9)(1,9)2(sc2,sc4)(2,4)3(sc6,sc7)(6,7)4(sc1,sc5)(1,5)5(sc4,sc8)(4,8)6(sc3,sc9)(3,9)7(sc5,sc7)(5,7)8(sc3,sc8)(2,8)9(sc3,sc7)(3,7)

 SQL Server用一个叫做稀疏向量(sparse vector)的行来跟踪Sparse列的存储。
稀疏向量的里的字节：

NameNumber of BytesMeaningComplex column Header205Sparse column Count2numbers of sparse columnsColumn ID Set2* the number of sparse columnstwo bytes for the column ID of each colun in the table with a value stored in the sparse vectorColumnOffset Table2* the number of sparse columnstwo bytes for the offset of the ending position of each sparse columnSparse DataDepends on actural valuesData

我们看一个例子：

USE testdb;GOIF EXISTS (SELECT * FROM sys.tables WHERE name = 'sparse_bits') DROP TABLE sparse_bits;GOCREATE TABLE sparse_bits(c1 int IDENTITY,c2 varchar(4),c3 char(4) SPARSE,c4 varchar(4) SPARSE);GOINSERT INTO sparse_bits SELECT 'aaaa', 'bbbb', 'cccc';INSERT INTO sparse_bits SELECT 'dddd', null, 'eeee';INSERT INTO sparse_bits SELECT 'ffff', null, 'gg';GO
现在我们使用DBCC IND命令查找存储三行的数据页的页数量，用DBCC Page查看页的字节。

 

稀疏向量的字节偏移：

字节交换后的值：

关于sparse vector，请注意以下几点：

1、sparse vector不会出现关于null值列的信息

2、对于稀疏向量，存储固定长度和可变长度的字符串没有区别。但是一个稀疏varchar列（不符合8060字节）能被存储在行溢出数据，而一个稀疏char列不可以。

3、因为只有两个字节存储稀疏列的数量，这就是数量的限制。

4、两字节的复杂列的头部显示可能是另外一个复杂列的类型，可能是向后指针(back-Poiner)（对应前文的转向指针forward-pointer)

 ■元数据

 非常小的特殊元数据被用来支持稀疏列。目录视图sys.columns包含两个列，用来跟踪表的Sparse column,每个列仅有两个可能的值0或1：is_Sparse和is_column_set，属性函数有两个值与稀疏列有关：isSarse和isColumnSet

使用Sparse的列：

SELECT OBJECT_NAME(object_id) as 'Table', name as 'Column', is_sparse, is_column_setFROM sys.columnsWHERE OBJECT_NAME(object_id) like '%sparse%';
使用列集的表：
SELECT OBJECT_NAME(object_id) as 'Table', name as 'Column'FROM sys.columnsWHERE COLUMNPROPERTY(object_id, name, 'IsColumnSet') = 1;
 ■稀疏列存储方面的节省

 Sparse column 主要是为了解决大部分为null值的表而设置的。但同时那些不是null的列却将要花费更多的空间存储开销。我们用一个例子(插入10万数据)说明：

 USE testdb;GOSET NOCOUNT ON;GOIF EXISTS (SELECT * FROM sys.tables WHERE name = 'sparse_nonulls_size') DROP TABLE sparse_nonulls_size;GOCREATE TABLE sparse_nonulls_size(col1 int IDENTITY, col2 datetime SPARSE, col3 char(10) SPARSE );GOIF EXISTS (SELECT * FROM sys.tables WHERE name = 'nonsparse_nonulls_size') DROP TABLE nonsparse_nonulls_size;GOCREATE TABLE nonsparse_nonulls_size(col1 int IDENTITY, col2 datetime, col3 char(10) );GOIF EXISTS (SELECT * FROM sys.tables WHERE name = 'sparse_nulls_size') DROP TABLE sparse_nulls_size;GOCREATE TABLE sparse_nulls_size(col1 int IDENTITY, col2 datetime SPARSE, col3 char(10) SPARSE );GOIF EXISTS (SELECT * FROM sys.tables WHERE name = 'nonsparse_nulls_size') DROP TABLE nonsparse_nulls_size;GOCREATE TABLE nonsparse_nulls_size(col1 int IDENTITY, col2 datetime, col3 char(10) );GODECLARE @num intSET @num = 1WHILE @num < 100000BEGIN INSERT INTO sparse_nonulls_size SELECT GETDATE(), 'my message'; INSERT INTO nonsparse_nonulls_size SELECT GETDATE(), 'my message'; INSERT INTO sparse_nulls_size SELECT NULL, NULL; INSERT INTO nonsparse_nulls_size SELECT NULL, NULL; SET @num = @num + 1;END;GO 

现在我们看一下每个表的页面数。下面的元数据查询（使用sys.allocation_units视图）：

注意：页面数最小的是有（null值）稀疏列的表。与没有稀疏列的表使用相同页面数的是列有null值或没有但是数据被定义成固定长度的表。这个空间比那个有null的稀疏列大两倍还多。最可怕的是定义了稀疏列，但这个列有not null值。

更多，请看MSDN：http://msdn.microsoft.com/en-us/library/cc280604.aspx

Fixed-Length Data Types

Data typeNonsparse bytesSparse bytesNULL percentage

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%

Precision-Dependent–Length Data Types

Data typeNonsparse bytesSparse bytesNULL percentage

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)

Use the decimal type as a conservative estimate.

  

Data-Dependent–Length Data Types

Data typeNonsparse bytesSparse bytesNULL percentage

sql_variant

Varies with the underlying data type

  

varchar or char

2*

4*

60%

nvarchar or nchar

2*

4*+

60%

varbinary or binary

2*

4*

60%

xml

2*

4*

60%

hierarchyid

2*

4*

60%

*The length is equal to the average of the data that is contained in the type, plus 2 or 4 bytes.

通常推荐的做法是：当你断定使用稀疏列能节省20%-40%的空间时使用稀疏列。至此，稀疏列告一段落，下一篇请看数据压缩。

 

 

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助!   [e-mail]