Nonclustered Columnstore Index on a Temp Table in SQL Server

Yes, you can build a nonclustered columnstore index on a temp table. No, you can’t build one on a table variable. The index pays off only when the temp table is big and you query it more than once.

Build a Nonclustered Columnstore Index on a Temp Table

A columnstore index stores each column in its own compressed segment. A query that reads three columns of five reads only those three. That suits summary queries over many rows. A nonclustered columnstore index adds this storage to an ordinary table. A temp table is an ordinary table that lives in tempdb.

The demo temp table holds a million sales lines. A formula fills it, so every run builds the same rows. Nothing here creates a database. The temp table disappears when your session ends.

DROP TABLE IF EXISTS #SalesLines;
CREATE TABLE #SalesLines (
    LineID    int          NOT NULL,
    ProductID int          NOT NULL,
    Qty       smallint     NOT NULL,
    UnitPrice decimal(8,2) NOT NULL,
    SoldOn    date         NOT NULL
);
INSERT INTO #SalesLines (LineID, ProductID, Qty, UnitPrice, SoldOn)
SELECT n, 1 + (n % 40), 1 + (n % 5), 5 + (n % 20), DATEADD(DAY, n % 365, '2026-01-01')
FROM (SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b CROSS JOIN sys.all_objects AS c) AS x;

First measure a summary query without the index. SET STATISTICS IO and TIME shows the reads and the time in the Messages tab.

SET STATISTICS IO, TIME ON;
GO
SELECT ProductID, COUNT(*) AS Lines, SUM(Qty * UnitPrice) AS Revenue
FROM #SalesLines
GROUP BY ProductID
ORDER BY ProductID;

Now build the index on the three columns the query reads, and run the same query again. The build prints its own time, which matters for the cost question later.

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesLines
ON #SalesLines (ProductID, Qty, UnitPrice);
GO
SELECT ProductID, COUNT(*) AS Lines, SUM(Qty * UnitPrice) AS Revenue
FROM #SalesLines
GROUP BY ProductID
ORDER BY ProductID;

Your times will differ, and the shape holds. The scan of the heap reads about 3,300 pages. The columnstore query reads compressed data, so it reports LOB reads and one segment read. The segment read is the sign that the index served the query. The query ran about five times faster. The build took about 24 times as long as one query on the heap.

What the Index Looks Like Inside

Rows go into compressed row groups of up to about a million rows. Small loads go to a delta store first. The next script lists the row groups, adds 1,000 rows and lists them again. The table stays writable, because updatable columnstore indexes arrived in SQL Server 2016.

SELECT rg.state_desc, rg.total_rows
FROM tempdb.sys.dm_db_column_store_row_group_physical_stats AS rg
WHERE rg.object_id = OBJECT_ID(N'tempdb..#SalesLines');

INSERT INTO #SalesLines (LineID, ProductID, Qty, UnitPrice, SoldOn)
SELECT TOP (1000) 1000000 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 1, 1, 9.99, '2026-12-31'
FROM sys.all_objects;

SELECT rg.state_desc, rg.total_rows
FROM tempdb.sys.dm_db_column_store_row_group_physical_stats AS rg
WHERE rg.object_id = OBJECT_ID(N'tempdb..#SalesLines');

The row group query can print a warning about an enforced join order. It comes from the system view, and the result is unaffected. The new rows wait in an open delta row group. A query still sees them, and a later reorganize compresses them. That is why a nonclustered columnstore index suits data that you load once and read many times.

Open rows don’t have to stay open. The next statement compresses them on demand. SQL Server also leaves a TOMBSTONE entry for the old delta group, and it removes that entry later.

ALTER INDEX NCCI_SalesLines ON #SalesLines
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

List the Columns Your Queries Read

The index covers only the columns you list. A query that groups by month of SoldOn reads a column outside the index. It ignores the index and scans the heap again. Here it read about 3,300 pages, as it did before the index. Add SoldOn to the index if your summaries need it. Every added column makes the build slower and the index bigger.

A Table Variable Does Not Allow It

Declare a table variable and try to add the index. The CREATE INDEX statement doesn’t accept a variable name at all. Run each block below as its own batch, because a variable ends at the next GO.

DECLARE @Lines TABLE (LineID int, ProductID int);
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Lines ON @Lines (ProductID);

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '@Lines'.

You can also define an index inside the declaration, which SQL Server 2014 and later allow for rowstore indexes. A columnstore index fails there too, with its own message.

DECLARE @Lines TABLE (
    LineID    int,
    ProductID int,
    INDEX NCCI_Lines NONCLUSTERED COLUMNSTORE (ProductID)
);

Msg 35310, Level 15, State 1, Line 4
The statement failed because columnstore indexes are not allowed on table types and table variables. Remove the column store index specification from the table type or table variable declaration.

A clustered columnstore index inside the declaration fails with the same message. If you need a nonclustered columnstore index on staged data, use a temp table or a real table.

Is It Worth the Build Cost?

You could argue that nobody needs this, because a temp table lives for one session. The numbers above agree for a single query. The build took about three seconds, and the query saved about a tenth of a second. You need to run roughly thirty summary queries on the same temp table before the index breaks even.

That pattern does exist. A report procedure loads a few million rows into a temp table, then produces ten summaries from it. Test your own workload, and measure the build as well as the query.

What to Remember

A nonclustered columnstore index works on a temp table, and a table variable can’t have one. Build it after the load, on the columns your summary queries read. It pays off when the table is large and you query it many times. Use SET STATISTICS IO and TIME to prove it, and include the build in the total.

Drop the temp table when you finish, or close the window. The first statement below turns the statistics display off.

SET STATISTICS IO, TIME OFF;
DROP TABLE IF EXISTS #SalesLines;

A columnstore index is not a speed setting, it is a trade of build time for read time.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.