SQL Server 2008 sparse columns to determine the transformation of columns

With the introduction of new sparse column functionality in SQL Server 2008, it is now possible to declare a column as a sparse column and to enter a null value in the column at any time and it will not consume any space. One technique is to find out when to determine whether a column is defined as a sparse column.

In the following example, address Lines 1 to 3 is required, address Lines 4 and 5 are not required but often used, and address Lines 6 to 8 are rarely used. When creating address Lines 6 to 8 o'clock, we use sparse options because we know that this data is rarely stored.

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,
)

So why not simply declare all the columns as sparse columns?

Sparse columns require an additional 4 bytes to store non-null value fixed-length data type values in the table and require 0 bytes to store a null value; Therefore, it is important to have the correct thresholds on each data type, or you can use more space instead of getting it to end. The fewer bytes a data type uses, the higher the percentage of empty values used to conserve space.

In one of the MSDN tables, there are recommended percentages for using sparse columns. Look at this to help you determine when you can get the benefit of using sparse columns.

Using this table as a guideline, the following script will confirm any columns that might get the new sparse column functionality. By searching for columns with null values in the database that exceed a certain threshold, you can easily analyze the results and determine whether the new feature is available. The threshold values for fixed-length columns are stored in a temporary table. Data types that depend on precision and length will default to 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 ' as VARCHAR (MAX)
From INFORMATION_SCHEMA. COLUMNS
JOIN sysobjects B
On INFORMATION_SCHEMA. COLUMNS. table_name = B.name
WHERE XTYPE = ' U '
--and information_schema. COLUMNS. Table_schema = ' person '
--and information_schema. COLUMNS. table_name = ' Contact '
EXEC (@SQL)
SELECT A.CLMN,
A.nullcount,
A.tablecount,
A.datatype,
(A.nullcount * 1.0/a.tablecount) Nullpercent,
ISNULL (b.prcent,60) * valuepercent
From #TMP A
Left JOIN #SPARCEPERCENTAGE B
On a.datatype = B.datatype
WHERE a.nullcount > 0
and (A.nullcount * 1.0/a.tablecount) >= ISNULL (b.prcent,60) *. 01
ORDER BY Nullpercent DESC
DROP TABLE #TMP
DROP TABLE #SPARCEPERCENTAGE