Sparse Column is one more new feature introduced in SQL SERVER 2008. Storing a null value in a sparse column doesn’t take any space, but storing a non-null value in sparse column takes 4 bytes extra space than the non-sparse columns of the same data type.<\/p>\n
For example: As we know that storing a null\/non-null value in a DATETIME column takes 8 bytes. On the other hand Sparse DATETIME column takes no space for storing null value\u00a0but storing a non-null value will take 12 bytes i.e. 4 bytes extra then normal DATETIME column.<\/p>\n
At this moment the obvious question which arises in our mind is: Why 0 bytes for null value and 4 bytes extra for storing non-null value in a sparse column? Reason for this is, sparse column’s value is not stored together with normal columns in a row, instead they are stored at the end of each row as special structure named Sparse Vector. Sparse vector structure contains:<\/p>\n
\u00a0[List of non-null Sparse Column Id’s \u2013 It takes 2 Bytes for each non-null Sparse column] + [List of Column Offsets — It takes 2 bytes for each non-null Sparse column].<\/p>\n
So, defining columns with high density of null value as Sparse will result in huge space saving. As explained previously non-null value in the sparse column is stored in a complex structure, so reading non-null sparse column value will have slight performance overhead.<\/p>\n
Let us understand the Sparse Column concept with below example.<\/p>\n
Example:\u00a0 <\/span>In this example we will create two identical tables. Only difference between them is, in one table two columns are marked as Sparse. In both of these tables insert some 25k records and check the space utilization by these tables.<\/p>\n \u00a0Now check the space used by these tables by executing the below statements:<\/p>\n Result:<\/p>\n So, with above example it is clear that defining a column with high density of null values result’s in huge space saving.<\/p>\n Please correct me, if\u00a0my understanding\u00a0is wrong. Comments are always welcome.<\/p>\n","protected":false},"excerpt":{"rendered":" Sparse Column is one more new feature introduced in SQL SERVER 2008. Storing a null value in a sparse column doesn’t take any space, but storing a non-null value in sparse column takes 4 bytes extra space than the non-sparse columns of the same data type. For example: As we know that storing a null\/non-null … Continue reading Sparse Columns in SQL Server 2008<\/span> CREATE DATABASE SPARSEDEMO\nGO\nUSE SPARSEDEMO\nGO\nCREATE TABLE SPARSECOLUMNTABLE\n(\n\u00a0col1 int identity(1,1),\n\u00a0col2 datetime sparse<\/span>,\n\u00a0col3 int sparse<\/span>\n)\nCREATE TABLE NONSPARSECOLUMNTABLE\n(\n\u00a0col1 int identity(1,1),\n\u00a0col2 datetime,\n\u00a0col3 int\n)\nGO\nINSERT INTO SPARSECOLUMNTABLE VALUES(NULL,NULL)\nINSERT INTO NONSPARSECOLUMNTABLE VALUES(NULL,NULL)\nGO 25000<\/pre>\nEXEC SP_Spaceused SPARSECOLUMNTABLE <\/span>\nEXEC SP_Spaceused NONSPARSECOLUMNTABLE<\/span><\/pre>\nname\u00a0 rows\u00a0 reserved\u00a0data\u00a0 index_size\u00a0unused\nSPARSECOLUMNTABLE\u00a025000\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0392 KB\u00a0 344 KB<\/span>\u00a08 KB\u00a0 40 KB\n\nname\u00a0 rows\u00a0 reserved\u00a0data\u00a0 index_size\u00a0unused\nNONSPARSECOLUMNTABLE\u00a025000\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0712 KB\u00a0 656 KB\u00a0<\/span>8 KB\u00a0 48 KB<\/pre>\n