Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

SQL Server - Sparse Columns avoid storage for NULL values

Oct 12 2011 3:56AM by Nirav   

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent.

Read More..   [0 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Nirav
37 · 5% · 1593
9
 
5
 
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

2  Comments  

  • We can assertain the sparse column identification throught he bewlo query:

    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 ''' + TABLESCHEMA + '.' + REPLACE(TABLENAME,'''','''''') + '.' + COLUMNNAME + ''' AS Clmn, count(*) NullCount, ''' + DATATYPE + ''', (Select count(*) FROM ' + TABLESCHEMA + '.[' + TABLENAME + ']) AS TableCount FROM ' + TABLESCHEMA + '.[' + TABLENAME + '] WHERE [' + COLUMNNAME + '] IS NULL ;' + CHAR(13) AS VARCHAR(MAX)) FROM INFORMATIONSCHEMA.COLUMNS JOIN sysobjects B ON INFORMATIONSCHEMA.COLUMNS.TABLENAME = B.NAME WHERE XTYPE = 'U' AND INFORMATIONSCHEMA.COLUMNS.TABLESCHEMA = 'dbo' AND INFORMATIONSCHEMA.COLUMNS.TABLENAME = 'SmgtTrackingWorkRequest' EXEC( @SQL) SELECT A.CLMN, A.NULLCOUNT, A.TABLECOUNT, A.DATATYPE, (A.NULLCOUNT * 1.0 / A.TABLECOUNT) NULLPERCENT, ISNULL(B.PRCENT,60) * .01 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

    commented on Oct 14 2011 4:34AM
    Latheesh NK
    51 · 4% · 1178

Your Comment


Sign Up or Login to post a comment.

"SQL Server - Sparse Columns avoid storage for NULL values" rated 5 out of 5 by 9 readers
SQL Server - Sparse Columns avoid storage for NULL values , 5.0 out of 5 based on 9 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]