Benny Austin

SSIS: ETL for tables with Columnstore Index

Columnstore Index on fact tables provide significant improvements in response time especially for aggregated queries. One of the first things I did after migrating my databases to SQL Server 2012 was to create Columnstore Index on fact tables. Now I can run aggregated queries that fetch results in reasonable response a time which is great for development, unit testing and helps testers to run their test scripts faster.

One caveat to be aware of is that the table on which a Columnstore Index is defined cannot be inserted, updated or deleted (at the time of writing this post).

“DELETE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the DELETE statement, then rebuilding the columnstore index after DELETE is complete.”

So your SSIS ETL package must first disable the Columnstore Index, then load data and finally rebuild the Columnstore Index as last step which enables the index (Screen Capture 1).

Screen Capture 1 - Disable CS Index, Load, Rebuild CS Index

Screen Capture 1 – Disable CS Index, Load, Rebuild CS Index

In an ETL framework this could be automated by disabling all Columnstore Indexes before loading fact tables and rebuilding all Columnstore Indexes after all fact tables have been loaded.

--Get List of Columnstore Indexes
SELECT obj.name, idx.name
FROM sys.indexes AS idx
	INNER JOIN sys.objects AS obj
		on obj.object_id =idx.object_id
		AND obj.name like 'FACT%'
		AND idx.type =6 --NONCLUSTERED COLUMNSTORE

 

--Disable Columnstore Index
ALTER INDEX CSI_FACT_TABLE ON dbo.FACT_TABLE DISABLE

 

--Rebuild/Enable Columnstore Index
ALTER INDEX CSI_FACT_TABLE ON dbo.FACT_TABLE REBUILD

Benny Austin

About these ads

One comment on “SSIS: ETL for tables with Columnstore Index

  1. Pingback: SQL Server “Denali”: Project Apollo/Columnstore Indexes | James Serra's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Copyright

© Benny Austin.
Follow

Get every new post delivered to your Inbox.

Join 198 other followers

%d bloggers like this: