Recently I was delivering training on SQL Server Performance Tuning and during the session, I received a very simple question about the nonclustered columnstore index on a temp table.
The question was is it possible to create columnstore index on a temp table or not. Honestly, when I heard this question, I was not sure about the answer as I personally have never created a columnstore index on the temp table. However, once I was back on the hotel and tried that out and the answer is YES, just like any other index, we can create a columnstore index on the temp table. Here is the script which demonstrate the same.
-- Create a Temp Table CREATE TABLE [dbo].[#MySalesOrderDetail]( [SalesOrderID] [int] NOT NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL ) ON [PRIMARY] GO -- Create a nonclustered columnstore index CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MySalesOrderDetail_ColumnStore] ON [#MySalesOrderDetail] (UnitPrice, OrderQty, ProductID) GO
However, when I try to create a nonclustered columnstore index on the table variable, it is actually not possible to do so. Here is the example for the same.
-- Declare a Table Variable DECLARE @MySalesOrderDetail TABLE( [SalesOrderID] [int] NOT NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL ) GO -- Create a nonclustered index CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MySalesOrderDetail_ColumnStore] ON @MySalesOrderDetail (UnitPrice, OrderQty, ProductID) GO
When we try to create a columnstore index on the table variable it gives us the following error:
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ‘@MySalesOrderDetail’
Well there you have the answer. Yes it is possible to create a nonclustered columnstore index on the temp table but not the table variable. This is one more reason, why one should consider using temp tables over table variables.
Let me know if you have ever faced a situation where you have to work with the columnstore index along with the temporary tables or table variables?
Here are a few additional blog posts on this subject, which you may find interesting.
- SQL SERVER – ColumnStore Indexes Without Aggregation
- How to List All ColumnStore Indexes with Table Name in SQL Server? – Interview Question of the Week #153
- SQL SERVER – Fix Error Msg 35336 Working with ColumnStore Indexes
- SQL SERVER – Fundamentals of Columnstore Index
- SQL SERVER – Columnstore Index Cannot be Created When Computed Columns Exist
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Can you inline it when creating the table var?
Hi pinal,
First congratulations you that you have been blogging for I guess for last 13 years daily,
But I can not think of use cases when we need to create a column stored index on temporary object, neither it is advisable
Or you can share some examples where we should do that.
Thank you for your comment. While creating a columnstore index on a temporary object may not be common or advisable in most cases, there are scenarios where it can be beneficial. For example, it can improve performance in large data sets, enhance analytical workloads, or facilitate storing historical data for analysis or reporting purposes. However, it’s important to thoroughly evaluate the specific workload and performance requirements before implementing such an index.
Not a recent post but wondering indeed if there are use cases as Neeraj pointed out?
I guess I missed answering it earlier. Finally got it.
Just voting for an answer to Neeraj’s comment – good question… ??
Indeed good question.