/*******************************************************************
*	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