[SQL SERVER] Compression table estimation espace
CREATE OR ALTER FUNCTION dbo.generate_index_ddl
(
@object_id INT,
@index_id INT,
@data_compression NVARCHAR(60) = NULL,
@sample_table SYSNAME,
@sample_index SYSNAME,
@desired_compression NVARCHAR(60) = NULL,
@xml_compression NVARCHAR(60) = NULL
)
RETURNS TABLE
AS
RETURN
(
WITH index_info AS (
SELECT
s.name AS schema_name,
o.name AS table_name,
i.name AS index_name,
i.is_primary_key,
i.is_unique,
i.type_desc,
i.filter_definition,
i.fill_factor,
i.allow_row_locks,
i.allow_page_locks,
i.is_padded,
i.ignore_dup_key,
i.data_space_id
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id = o.object_id
JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE i.object_id = @object_id AND i.index_id = @index_id
),
cols AS (
SELECT
ic.object_id,
ic.index_id,
ic.is_included_column,
c.name AS colname,
ic.is_descending_key,
ic.key_ordinal
FROM sys.index_columns ic
JOIN sys.columns c
ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE ic.object_id = @object_id AND ic.index_id = @index_id
),
key_cols AS (
SELECT STRING_AGG(QUOTENAME(colname) + CASE is_descending_key WHEN 1 THEN ' DESC' ELSE ' ASC' END, ', ') AS key_cols
FROM cols WHERE is_included_column = 0
),
include_cols AS (
SELECT STRING_AGG(QUOTENAME(colname), ', ') AS include_cols
FROM cols WHERE is_included_column = 1
),
ds AS (
SELECT name AS filegroup_name FROM sys.data_spaces ds
JOIN index_info i ON ds.data_space_id = i.data_space_id
)
SELECT
-- Drop scripts
N'IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = N''' + @sample_index + ''' AND object_id = OBJECT_ID(N''' + @sample_table + ''')) ' +
N'DROP INDEX ' + QUOTENAME(@sample_index) + N' ON ' + @sample_table AS drop_current_index_ddl,
N'IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = N''' + @sample_index + '_desired'' AND object_id = OBJECT_ID(N''' + @sample_table + ''')) ' +
N'DROP INDEX ' + QUOTENAME(@sample_index + '_desired') + N' ON ' + @sample_table AS drop_desired_index_ddl,
-- Create scripts (current)
N'CREATE ' +
CASE WHEN i.is_unique = 1 THEN N'UNIQUE ' ELSE N'' END +
i.type_desc + N' INDEX ' + QUOTENAME(@sample_index) +
N' ON ' + @sample_table +
N' (' + kc.key_cols + N')' +
CASE WHEN ic.include_cols IS NOT NULL THEN N' INCLUDE (' + ic.include_cols + N')' ELSE N'' END +
CASE WHEN i.filter_definition IS NOT NULL THEN N' WHERE ' + i.filter_definition ELSE N'' END +
N' WITH (DATA_COMPRESSION = ' + ISNULL(@data_compression, 'NONE') + N')' +
N' ON ' + QUOTENAME(ds.filegroup_name) AS create_current_index_ddl,
--Create scripts (desired compression)
N'CREATE ' +
CASE WHEN i.is_unique = 1 THEN N'UNIQUE ' ELSE N'' END +
i.type_desc + N' INDEX ' + QUOTENAME(@sample_index + '_desired') +
N' ON ' + @sample_table +
N' (' + kc.key_cols + N')' +
CASE WHEN ic.include_cols IS NOT NULL THEN N' INCLUDE (' + ic.include_cols + N')' ELSE N'' END +
CASE WHEN i.filter_definition IS NOT NULL THEN N' WHERE ' + i.filter_definition ELSE N'' END +
N' WITH (DATA_COMPRESSION = ' + ISNULL(@desired_compression, 'NONE') + N')' +
N' ON ' + QUOTENAME(ds.filegroup_name) AS create_desired_index_ddl,
--Compression commands
N'ALTER INDEX ' + QUOTENAME(@sample_index) + N' ON ' + @sample_table +
N' REBUILD WITH (DATA_COMPRESSION = ' + ISNULL(@data_compression, 'NONE') + N')' AS compress_current_ddl,
N'ALTER INDEX ' + QUOTENAME(@sample_index + '_desired') + N' ON ' + @sample_table +
N' REBUILD WITH (DATA_COMPRESSION = ' + ISNULL(@desired_compression, 'NONE') + N')' AS compress_desired_ddl,
i.is_primary_key AS is_primary
FROM index_info i
CROSS JOIN key_cols kc
CROSS JOIN include_cols ic
CROSS JOIN ds
)