/******************************************************************* * Project: I-Tech SQL Scripts * Keywords: [Administration][Analysis][MetaData] * Name: ADMIN.Sparse.Column.Calculations.sql * Version: 1.0 * Editions: 2008, 2008 R2, 2012 * Prereqs: [dbo].[prcNaGenerateNullAnalysisAdHoc_select] * Copyright (C) 2014 I-Tech Solutions, Inc. under Microsoft Reciprocal License (MS-RL) http://www.microsoft.com/en-us/openness/licenses.aspx * * Author: Date: Version: Summary: * Ben Johnston 2014-04-30 1.0 Created * * Purpose: Shows which columns might benefit from being defined as sparse columns. * Uses the MSDN supplied thresholds. * For data types with multiple definitions the more conservative * value is used for all types. * * Notes: Set the following variables: * @strDatabase Database * @strSchema Schema * @strTable Table * @strComment Comment - optional * * Run the script from the same database as the * table being analyzed. ********************************************************************/ DECLARE @SQL varchar(max) DECLARE @Database varchar(255) ,@Schema varchar(255) ,@Table varchar(255) ,@Comment varchar(1000) SELECT @Database = 'AdventureWorks2012' ,@Schema = 'Production' ,@Table = 'Product' ,@Comment = 'Test Sparse Column Calculation' CREATE TABLE #SPARSE ( sparseDatabase varchar(255) ,sparseSchema varchar(255) ,sparseObject varchar(255) ,sparseComment varchar(1000) ,sparseRowCount bigint ,sparseColumnName varchar(255) ,sparseNonNull bigint ,sparseDatatype varchar(30) ,sparseNull AS (sparseRowCount - sparseNonNull) ,sparseNullPercentage AS CONVERT(decimal(4,3),((CONVERT(decimal(18,2),sparseRowCount) - sparseNonNull) / sparseRowCount)) ,sparseNullThreshold decimal(2,2) ) CREATE TABLE #SparseThreshold ( Datatype varchar(30) ,Threshold decimal(2,2) CONSTRAINT chkThreshold CHECK (Threshold BETWEEN .00 AND .99) --percentage expressed as an decimal .00 - .99 ,Precision tinyint ) --Threshold values taken from MSDN / SQL BOL - http://msdn.microsoft.com/en-us/library/cc280604.aspx INSERT INTO #SparseThreshold ( Datatype ,Threshold ,Precision ) SELECT 'bit',.98,NULL UNION SELECT 'tinyint',.86,NULL UNION SELECT 'smallint',.76,NULL UNION SELECT 'int',.64,NULL UNION SELECT 'bigint',.52,NULL UNION SELECT 'real',.64,NULL UNION SELECT 'float',.52,NULL UNION SELECT 'smallmoney',.64,NULL UNION SELECT 'money',.52,NULL UNION SELECT 'smalldatetime',.64,NULL UNION SELECT 'datetime',.52,NULL UNION SELECT 'uniqueidentifier',.43,NULL UNION SELECT 'date',.69,NULL UNION SELECT 'datetime2',.57,NULL UNION --SELECT 'datetime2',.52,NULL -- UNION SELECT 'time',.69,NULL UNION --SELECT 'time',.60,NULL -- UNION SELECT 'datetimetoffset',.52,NULL UNION --SELECT 'datetimetoffset',.49,NULL --UNION SELECT 'decimal',.60,NULL UNION SELECT 'numeric',.60,NULL UNION --SELECT 'decimal',.42,NULL --UNION --SELECT 'numeric',.42,NULL -- UNION SELECT 'vardecimal',.60,NULL --Use the decimal type as a conservative estimate. UNION SELECT 'varchar',.60,NULL UNION SELECT 'char',.60,NULL UNION SELECT 'nvarchar',.60,NULL UNION SELECT 'nchar',.60,NULL UNION SELECT 'varbinary',.60,NULL UNION SELECT 'binary',.60,NULL UNION SELECT 'xml',.60,NULL UNION SELECT 'hierarchyid',.60,NULL --Generate NULL analysis statement --Cannot assign to variable at time of EXEC - generats nested runtime error due to dynamic SQL EXEC Utility.[dbo].[prcNaGenerateNullAnalysisAdHoc_select] @Database ,@Schema ,@Table ,@Comment ,@SQL OUTPUT ,0 INSERT INTO #SPARSE ( sparseDatabase ,sparseSchema ,sparseObject ,sparseComment ,sparseRowCount ,sparseColumnName ,sparseNonNull ) EXEC(@SQL) UPDATE #SPARSE SET sparseDatatype = st.name FROM sys.objects SO INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id INNER JOIN sys.columns SC ON so.object_id = sc.object_id INNER JOIN sys.types st ON sc.system_type_id = st.system_type_id INNER JOIN #SPARSE ON ss.name = sparseSchema AND so.name = sparseObject AND sc.name = sparseColumnName UPDATE #SPARSE SET sparseNullThreshold = Threshold FROM #SparseThreshold INNER JOIN #SPARSE ON sparseDatatype = Datatype SELECT * ,CASE WHEN sparseNullThreshold <= sparseNullPercentage THEN 1 ELSE 0 END sparseThresholdMet FROM #SPARSE DROP TABLE #SPARSE DROP TABLE #SparseThreshold