用于创建非聚集列存储索引的存储过程

Question

我需要一个存储过程，它将接受表名作为参数，然后在每列上创建非聚集列存储索引。

有人能帮我吗？

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

Answer 1

我想我设法找到了一个解决方案。

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