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.

SQL SERVER - Creating a Nonclustered Columnstore Index on Temp Table nonclustered-columnstore-800x184

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

ColumnStore Index, SQL Index, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Getting Second Row from Table
Next Post
SQL SERVER – Stored Procedure and RETURN Keyword

Related Posts

7 Comments. Leave new

  • Peter D Daniels
    November 1, 2019 8:46 am

    Can you inline it when creating the table var?

    Reply
  • 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.

    Reply
    • 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.

      Reply
  • Not a recent post but wondering indeed if there are use cases as Neeraj pointed out?

    Reply
  • Just voting for an answer to Neeraj’s comment – good question… ??

    Reply

Leave a Reply