SQL Server, TSQL

T-SQL Script to Dynamically Create Table, Build Clustered Columnstore Index, and Partition Switch

2 Comments
Recently myself and Mitchell Pearson (blog|twitter) were working on a project for a client that required us to load a ton of data (dozens of TBs) into some tables each built with a clustered columnstore index. We discovered during testing that the fastest way to get that much data into the clustered columnstore index is to create an empty uncompressed table, load the data into the uncompressed table, then apply the clustered columnstore index to the table, and partition switch the data into the main table. In order to facilitate this, I created this script to dynamically create a copy of the target table (without the columnstore index), create the clustered columnstore index, and then do the partition switch automatically.

I hope you find this script helpful as an example.

DECLARE @DEBUG INT = 1

DECLARE @sql NVARCHAR(MAX),
    @cols NVARCHAR(MAX) = N'',
    @targettable nvarchar(max) = N'FactInternetSales', -- Target table 
    @partitionrange varchar(8) = 20110101, -- The partition range value you are loading
    @partitioncolumn varchar(100) = 'OrderDateKey' -- The column in the fact table you wish to partition your table by
    
-- GET PARTITION NUMBER 
declare @partitionnumber varchar(25)
Select @partitionnumber = $PARTITION.RangePF1(@partitionrange) -- Lookup the partition number based on the range value. This is needed for the partition switch

-- PREPARE SYNTAX TO CREATE NEW TABLE
declare @newtable nvarchar(max) = @targettable + '_' + @partitionrange

SELECT @cols += N', [' + name + '] ' + system_type_name + case is_nullable when 1 then ' NULL' else ' NOT NULL' end
 FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.'+ @targettable , NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE '+ @newtable + '(' + @cols + ') '

-- CREATE TABLE 
Set @sql = @sql + ' on ps_daily(' + @partitioncolumn + ');' -- Use the Partition Scheme in your environment

IF @DEBUG = 1
    BEGIN 
        PRINT @sql;
    END
        ELSE 
            BEGIN
                EXEC sp_executesql @sql PRINT @sql
            END

-- CREATE CCI
SET @sql = 
'CREATE CLUSTERED COLUMNSTORE INDEX CCIX_' + @newtable + ' ON ' + @newtable 

IF @DEBUG = 1
    BEGIN 
        PRINT @sql 
    END
        ELSE 
            BEGIN
                EXEC sp_executesql @sql PRINT @sql
            END
/*

Add your logic here to insert your data into the Clustered Columnstore Index table

*/

-- PARTITION SWITCH PARTITION
Set @sql = 
'ALTER TABLE ' + @newtable + ' SWITCH PARTITION '+ @partitionnumber +' TO ' + @targettable + ' PARTITION '+ @partitionnumber 

IF @DEBUG = 1
    BEGIN 
        PRINT @sql 
    END
        ELSE 
            BEGIN
                EXEC sp_executesql @sql PRINT @sql  
            END


-- DROP OLD TABLE
set @sql = 
'DROP TABLE ' + @newtable 

IF @DEBUG = 1
    BEGIN 
        print @sql 
    END
        ELSE 
            BEGIN
                EXEC sp_executesql @sql print @sql
            END

 

DISCLAIMER: Here’s the part where I say its not my fault if you blow up your environment because you ran some script you downloaded off the internet. Don’t run it in prod until you’ve verified it. 🙂

2 thoughts on “T-SQL Script to Dynamically Create Table, Build Clustered Columnstore Index, and Partition Switch”

  1. Good post .
    I have partition 1 and 2 which contains data .
    my requirement is to merge 1 & 2 .
    So i tried your code to move data from my partition 1 to staging table and then merge 1&2 . but while moving back to data from staging table to partition 1 again i am getting below error .
    ALTER TABLE SWITCH statement failed. The specified partition 1 of target table ***’ must be empty.

Comments are closed.