SQL Server: Columnstore Index on Temp Tables June 20th, 2014

Vinod Kumar

Recently I was talking to a customer during performance testing and they had an interesting twist to using Columnstore Index. Since they were on SQL Server 2012, I had suggested them to use Non-Clustered Columnstore index for their workload. He started to quiz me with a number of questions and I thought it would be appropriate to point them to some resources I had already written. So I took the liberty to send them the two articles:

  1. SQL Server 2012: ColumnStore Characteristics
  2. SQL Server 2012: ColumnStore Part II

The fundamentals and concepts have not changed but where worth a mention and read. Having said that, the counter question after about an hour of their research and reading the contents. “Will it work with Temp Tables? We are doing a reporting workload and are using Temp tables extensively.”

I thought, wow, interestingly I have not written on it till date and is worth a mention though. The easy answer is “YES”, it is quite possible. Why didnt you try was my answer :)

Columnstore Index on Temp Tables

To drive home the concept, in the below example I have created a simple temp table called as #Temp_Account. When I query the table, the execution plan shows as “RowStore” under storage as shown below.

The next logical step is to create our Non-Clustered Columnstore Index. I am using the standard syntax as shown below.

If we try to insert into this temp table, it is still readonly because non-clustered columnstore index has been defined in the table.

Msg 35330, Level 15, State 1, Line 19
INSERT statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, and then rebuilding the columnstore index after INSERT has completed.

After index creation, if we make a simple query we can see the change in Execution Plan. The storage is now showing as “ColumnStore” as shown in the execution plan below.

The above query is simple in nature and hence the execution mode is “Row”. In future posts I will show how this can be changed to Batch mode.

Tags: , , , , , , , , ,

This entry was posted on Friday, June 20th, 2014 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply