MS SQL Serverのスパース列またはスパース列。 実際のアプリケーションエクスペリエンス

SELECT indexes.name AS index_name, objects.name AS object_name, objects.type_desc AS object_type_description, COUNT(*) AS buffer_cache_pages, COUNT(*) * 8 / 1024 AS buffer_cache_used_MB FROM sys.dm_os_buffer_descriptors INNER JOIN sys.allocation_units ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id INNER JOIN sys.partitions ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3)) OR (allocation_units.container_id = partitions.partition_id AND type IN (2))) INNER JOIN sys.objects ON partitions.object_id = objects.object_id INNER JOIN sys.indexes ON objects.object_id = indexes.object_id AND partitions.index_id = indexes.index_id WHERE allocation_units.type IN (1,2,3) AND objects.is_ms_shipped = 0 AND dm_os_buffer_descriptors.database_id = DB_ID() GROUP BY indexes.name, objects.name, objects.type_desc ORDER BY COUNT(*) DESC; 

 CREATE TABLE #temp ( ColumnName varchar(50), ColumnID int, TableName varchar(50), TableId int, TypeName varchar(50), IsParse bit, IsNullable bit, NumberOfRow bigint, NumberOfRowNULL bigint, Ratio int) SET NOCOUNT ON INSERT into #temp SELECT DISTINCT sys.columns.name ColumnName, sys.columns.column_id ColumnID, OBJECT_NAME(sys.columns.object_id) AS TableName, sys.columns.object_id TableID, CASE systypes.name WHEN 'sysname' THEN 'nvarchar' ELSE systypes.name END AS TypeName, sys.columns.is_sparse IsParse, sys.columns.is_nullable IsNullable, 0,0,0 FROM sys.columns (NoLock) INNER JOIN systypes (NoLock) ON systypes.xtype = sys.columns.system_type_id WHERE sys.columns.object_id = OBJECT_ID('my_table') -- change table name and systypes.name NOT IN ('geography', 'geometry', 'image', 'ntext', 'text', 'timestamp') and sys.columns.is_sparse = 0 and sys.columns.is_nullable = 1 and sys.columns.is_rowguidcol = 0 and sys.columns.is_identity = 0 and sys.columns.is_computed = 0 and sys.columns.is_filestream = 0 and sys.columns.default_object_id = 0 and sys.columns.rule_object_id = 0 and sys.columns.system_type_id=sys.columns.user_type_id delete tps from #temp tps where exists ( select DISTINCT 'Exists' from sys.columns inner join sys.indexes i on i.object_id = tps.TableId inner join sys.index_columns ic on ic.column_id = tps.ColumnID inner join sys.columns c on c.object_id = tps.TableId and ic.column_id = c.column_id where i.type =1 or i.is_primary_key = 1) select count(*) from #temp delete tps from #temp tps inner join sys.partitions p on p.object_id = tps.TableId where p.data_compression<>0; DECLARE @TableName nvarchar(1000) DECLARE @ColumnName nvarchar(1000) DECLARE @vQuery nvarchar(1000) DECLARE @result1 INT DECLARE @result2 INT DECLARE tables_cursor CURSOR FAST_FORWARD FOR SELECT TableName,ColumnName FROM #temp OPEN tables_cursor FETCH NEXT FROM tables_cursor INTO @TableName,@ColumnName WHILE @@FETCH_STATUS = 0 BEGIN -- Search the number of row in a table SET @vQuery = 'SELECT @result1= COUNT(*) FROM [' + @TableName + ']' EXEC SP_EXECUTESQL @Query = @vQuery , @Params = N'@result1 INT OUTPUT' , @result1 = @result1 OUTPUT -- Search the number of row in a table SET @vQuery = 'SELECT @result2= COUNT(*) FROM [' + @TableName + '] where [' + @ColumnName + '] is null' EXEC SP_EXECUTESQL @Query = @vQuery , @Params = N'@result2 INT OUTPUT' , @result2 = @result2 OUTPUT update #temp set NumberOfRow = @result1,NumberOfRowNULL = @result2,Ratio = (@result2*100/@result1) where ColumnName=@ColumnName and TableName=@TableName FETCH NEXT FROM tables_cursor INTO @TableName,@ColumnName END CLOSE tables_cursor DEALLOCATE tables_cursor --delete from #temp where Ratio>10 select * from #temp --drop table #temp