如何向含Clustered Columnstore Index的表插入数据？附逻辑与代码

你的思路完全没问题——对于批量插入场景，先删除聚集列存储索引（CCI）、插入数据后再重建CCI，是提升插入性能的经典优化手段，因为列存储索引在频繁写入时的维护开销远高于堆表或行存储索引。下面是完善后的完整实现代码，以及关键细节的说明：

完整T-SQL实现 #

DECLARE @sql NVARCHAR(MAX)
DECLARE @tableName NVARCHAR(128) = 'cci_table'

-- 检查目标表是否存在聚集列存储索引（sys.indexes.type=6对应聚集列存储，type=5为非聚集列存储）
IF EXISTS (
    SELECT i.name, t.name
    FROM sys.indexes i
    JOIN sys.tables t ON i.object_id = t.object_id
    WHERE i.type = 6 -- 仅检查聚集列存储索引，若需包含非聚集可改为IN(5,6)
      AND t.name = @tableName
)
BEGIN
    -- 动态生成删除索引的SQL（避免硬编码索引名，兼容不同命名规范）
    SELECT @sql = N'DROP INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(@tableName)
    FROM sys.indexes i
    JOIN sys.tables t ON i.object_id = t.object_id
    WHERE i.type = 6
      AND t.name = @tableName

    EXEC sp_executesql @sql
    PRINT '已成功删除聚集列存储索引'
END

-- 执行批量数据插入（替换为你的实际插入逻辑，推荐用批量方式而非单行插入）
PRINT '开始插入数据...'
INSERT INTO cci_table (col1, col2, col3)
SELECT source_col1, source_col2, source_col3 
FROM your_source_table -- 示例：从源表批量导入
-- 也可使用BULK INSERT、OPENROWSET等更高效的批量导入方式

-- 重新创建聚集列存储索引
PRINT '开始重建聚集列存储索引...'
SET @sql = N'CREATE CLUSTERED COLUMNSTORE INDEX CCI_' + @tableName + N' ON ' + QUOTENAME(@tableName)
-- 可按需添加索引选项，比如指定并行度、压缩延迟等
-- SET @sql = N'CREATE CLUSTERED COLUMNSTORE INDEX CCI_' + @tableName + N' ON ' + QUOTENAME(@tableName) + N' WITH (MAXDOP = 4, COMPRESSION_DELAY = 0)'

EXEC sp_executesql @sql
PRINT '聚集列存储索引重建完成'

关键细节说明 #

1. 精准索引检查：sys.indexes.type=6专门匹配聚集列存储索引，如果你需要同时处理非聚集列存储索引，可以改为IN(5,6)，但通常批量插入优化针对的是聚集列存储。
2. 动态SQL的安全性：使用QUOTENAME()函数处理表名和索引名，避免特殊字符导致的语法错误，同时防范SQL注入风险。
3. 批量插入优先：删除CCI后表变为堆表，此时批量插入（如INSERT SELECT、BULK INSERT）的性能远高于单行插入，尽量避免循环插入单条数据。
4. 事务一致性：如果需要保证操作的原子性（避免中途出错导致表无索引），可以把整个逻辑包裹在事务中：

   BEGIN TRANSACTION
   BEGIN TRY
       -- 插入前删除CCI
       -- 批量插入数据
       -- 重建CCI
       COMMIT TRANSACTION
       PRINT '所有操作执行成功'
   END TRY
   BEGIN CATCH
       ROLLBACK TRANSACTION
       PRINT '操作失败，已回滚：' + ERROR_MESSAGE()
   END CATCH
   

5. 索引选项定制：创建CCI时可以根据业务需求添加选项，比如MAXDOP控制并行度、COMPRESSION_DELAY调整列存储延迟压缩策略，进一步优化性能。

内容的提问来源于stack exchange，提问作者alexithymia