SQL Server 2008稀疏列确定列的转化

【 tulaoshi.com - 编程语言 】

　　由于在SQL Server 2008中引入了新的稀疏列功能，现在就有可能把列声明为稀疏列并且任何时候在列中输入一个空值它都不会消耗任何空间。其中一个技巧是找出何时确定是否把一个列定义稀疏列。

　　在下面的例子中，Address Lines 1 到 3是要求的，Address Lines 4和5是不要求的但是经常使用的，Address Lines 6到8很少使用。当创建Address Lines 6到8时，我们使用稀疏选项，这是因为我们知道这个数据很少存储下来。

　　 CREATE TABLE CustomerInfo
　　(CustomerID INT PRIMARY KEY,
　　Address_Line1 VARCHAR(100) NOT NULL,
　　Address_Line2 VARCHAR(100) NOT NULL,
　　Address_Line3 VARCHAR(100) NOT NULL,
　　Address_Line4 VARCHAR(100) NULL,
　　Address_Line5 VARCHAR(100) NULL,
　　Address_Line6 VARCHAR(100) SPARSE NULL,
　　Address_Line7 VARCHAR(100) SPARSE NULL,
　　Address_Line8 VARCHAR(100) SPARSE NULL,
　　)

　　那么为什么不干脆把所有的列都声明为稀疏列呢？

　　稀疏列需要额外的4个字节来在表中存储非空值固定长度数据类型值并且要求零字节来存储一个空值;因此，在每一个数据类型上拥有正确的阈值是很重要的，或者你可以使用更多的空间而不是获得它来结束。一个数据类型使用的字节越少，用来节约空间的空值百分比要求就越高。

　　在MSDN的一张表中有使用稀疏列的建议百分比。看看这个可以帮助你确定何时可以获得使用稀疏列的益处。

　　使用这张表作为准则，下面的脚本将会确认任何可能获得新的稀疏列功能的列。通过搜索数据库中超过一定阈值的空值的列，你可以很容易地分析结果并且确定这个新功能是否可用。固定长度列的阈值存储在一个临时表中。依赖于精确度和长度的数据类型将默认为60%。

　　　USE AdventureWorks
　　GO
　　SET NOCOUNT ON
　　DECLARE @SQL VARCHAR(MAX)
　　CREATE TABLE #SPARCEPERCENTAGE (
　　DATATYPE VARCHAR(50),
　　PRCENT INT)
　　INSERT INTO #SPARCEPERCENTAGE
　　SELECT 'bit', 98
　　UNION ALL
　　SELECT 'tinyint', 86
　　UNION ALL
　　SELECT 'smallint', 76
　　UNION ALL
　　SELECT 'int', 64
　　UNION ALL
　　SELECT 'bigint', 52
　　UNION ALL
　　SELECT 'real', 64
　　UNION ALL
　　SELECT 'float', 52
　　UNION ALL
　　SELECT 'smallmoney', 64
　　UNION ALL
　　SELECT 'money', 52
　　UNION ALL
　　SELECT 'smalldatetime', 64
　　UNION ALL
　　SELECT 'datetime', 52
　　UNION ALL
　　SELECT 'uniqueidentifier', 43
　　UNION ALL
　　SELECT 'date', 69
　　CREATE TABLE #TMP (
　　CLMN VARCHAR(500),
　　NULLCOUNT INT,
　　DATATYPE VARCHAR(50),
　　TABLECOUNT INT)
　　SELECT @SQL = COALESCE(@SQL,'') + CAST('INSERT INTO #TMP Select ''' + TABLE_SCHEMA + '.' + REPLACE(TABLE_NAME,'''','''''') + '.' + COLUMN_NAME + ''' AS Clmn, count(*) NullCount, ''' + DATA_TYPE + ''', (Select count(*) FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']) AS TableCount FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] IS NULL ;' + CHAR(13) AS VARCHAR(MAX))
　　FROM INFORMATION_SCHEMA.COLUMNS
　　JOIN sysobjects B
　　ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = B.NAME
　　WHERE XTYPE = 'U'

　　这是针对AdventureWorks数据库运行时输出结果的一个实例。NullPercent列应该与ValuePercent比较以确定使用稀疏列是否有优势。正如你在第一行所看到的，Sales.SalesOrderHeader列注释的所有行都是空值，因此NullPercent是100%而ValuePercent是60%，所以使用稀疏列是个很不错的选择。注意：上面的查询限制了输出结果，只显示了哪些列会获得使用稀疏列的优势。

　　图一

　　在以上代码中，我注释了两行，如下所示，可以用这两行来限制每一次搜索只在一张表中进行。只需取消这些模式并且在你想分析的表上更改模式和表的值。

　　--AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = 'Person'

　　--AND INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'Contact'

　　如果你尝试运行SQL 2000上的脚本，你就需要把Varchar(MAX)改成 Varchar(8000)，并且极有可能在一张表接着一张表的基础上搜索。