SQL SERVER – Creating a Nonclustered Columnstore Index on Temp Table

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)