Triumph Spitfire Triumph Spitfire - 7 months ago 8
SQL Question

Stored procedure to create non clustered columnstore index

I need a stored procedure which will accept table name as a parameter and then creates non-clustered column store indexes on each column.

Could someone help me with this?

CREATE NONCLUSTERED COLUMNSTORE INDEX XCSI_MY_TBL
ON dbo.MY_TBL
(
COL1,
COL2,
COL3,
COL4
) WITH (DROP_EXISTING = OFF) ON [PRIMARY]

Answer

I think I managed to find a solution.

CREATE PROC [dbo].[CRE_NCCS_IDX] @TBL_NM VARCHAR(100) AS
BEGIN

--DECLARING VARIABLES FOR DYNAMIC SQL
DECLARE @TBL TABLE(ID INT, TBL_NM VARCHAR(100), CLMN_NM VARCHAR(100))
DECLARE @COL_VAL NVARCHAR(4000)
DECLARE @SEL_SQL NVARCHAR(4000)
DECLARE @DROP_SQL VARCHAR(1000)

--INSERTING INTO TEMP TABLE
INSERT INTO @TBL
SELECT ROW_NUMBER() OVER(PARTITION BY COLUMN_NAME ORDER BY COLUMN_NAME) AS ID, 
    TABLE_NAME, 
    COLUMN_NAME  
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TBL_NM

--GETTING COLUMN VALUES AS COMMA SEPERATED STRING
SET @COL_VAL = (
SELECT STUFF((SELECT ', ' + CAST(CLMN_NM AS VARCHAR(1000)) [text()]
         FROM @TBL 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @TBL t
GROUP BY ID
)

--DROPPING INDEX IF EXISTS
SET @DROP_SQL = CONCAT('IF EXISTS(SELECT * FROM SYS.INDEXES WHERE OBJECT_ID = OBJECT_ID(''', @TBL_NM, ''') AND NAME =''XCSI_', @TBL_NM,
                            ''')   DROP INDEX XCSI_', @TBL_NM, ' ON dbo.', @TBL_NM)

EXEC (@DROP_SQL)

--EXECUTING DYNAMIC SQL FOR CREATING INDEX
SET @SEL_SQL = CONCAT('CREATE NONCLUSTERED COLUMNSTORE INDEX XCSI_', @TBL_NM, ' ON dbo.', @TBL_NM, ' (',@COL_VAL, ') WITH (DROP_EXISTING  = OFF) ON [PRIMARY]')
--SELECT @SEL_SQL
EXEC (@SEL_SQL)

END