Rowstore to Columnstore: Replace the Clustered Index

Rowstore to columnstore is a change of the clustered index, and one command works in only one case. A clustered columnstore index is the table itself, stored by column.

Two Tables, Two Kinds of Clustered Index

A table can have one clustered index of either kind, so the old rowstore index has to go. How it goes depends on whether a constraint owns it. The demo database RowstoreSwapDemo holds two tables of 600,000 rows. SaleKey has a primary key, and the key is the clustered index. SalePlain has a clustered index that is not a constraint. The two tables hold the same data, so the only difference is who owns the index. The cleanup at the end drops the database. The load needs a few seconds.

IF DB_ID(N'RowstoreSwapDemo') IS NULL CREATE DATABASE RowstoreSwapDemo;
GO
USE RowstoreSwapDemo;
GO
DROP TABLE IF EXISTS dbo.SaleKey;
DROP TABLE IF EXISTS dbo.SalePlain;
CREATE TABLE dbo.SaleKey (SaleID int NOT NULL CONSTRAINT PK_SaleKey PRIMARY KEY CLUSTERED, ProductID int NOT NULL, Quantity int NOT NULL, Amount decimal(10,2) NOT NULL, SaleDate date NOT NULL);
CREATE TABLE dbo.SalePlain (SaleID int NOT NULL, ProductID int NOT NULL, Quantity int NOT NULL, Amount decimal(10,2) NOT NULL, SaleDate date NOT NULL);
CREATE CLUSTERED INDEX CX_SalePlain ON dbo.SalePlain (SaleID);
WITH n AS (SELECT TOP (600000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b)
INSERT INTO dbo.SaleKey (SaleID, ProductID, Quantity, Amount, SaleDate)
SELECT i, i % 500 + 1, i % 9 + 1, (i % 300) + 0.99, DATEADD(DAY, i % 365, '2026-01-01') FROM n;
WITH n AS (SELECT TOP (600000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b)
INSERT INTO dbo.SalePlain (SaleID, ProductID, Quantity, Amount, SaleDate)
SELECT i, i % 500 + 1, i % 9 + 1, (i % 300) + 0.99, DATEADD(DAY, i % 365, '2026-01-01') FROM n;

This query measures the size of each table before the change. It counts the pages used by the clustered index, which holds the table.

SELECT OBJECT_NAME(object_id) AS TableName, SUM(used_page_count) AS UsedPages, SUM(row_count) AS TotalRows
FROM sys.dm_db_partition_stats
WHERE object_id IN (OBJECT_ID(N'dbo.SaleKey'), OBJECT_ID(N'dbo.SalePlain')) AND index_id IN (0, 1)
GROUP BY object_id
ORDER BY TableName;

The DROP_EXISTING Command Fails on a Key

The natural command for rowstore to columnstore uses DROP_EXISTING = ON. It rebuilds an index in one step, under the same name. Try it on the table whose clustered index is a primary key.

CREATE CLUSTERED COLUMNSTORE INDEX PK_SaleKey ON dbo.SaleKey WITH (DROP_EXISTING = ON);

Msg 1907, Level 16, State 1, Line 1
Cannot recreate index 'PK_SaleKey'. The new index definition does not match the constraint being enforced by the existing index.

The constraint insists on its own definition: a unique key. A columnstore index cannot enforce it, so SQL Server refuses to replace the index. Two other attempts fail as well. The first creates a columnstore index with no DROP_EXISTING. It fails with Msg 35372, which says a table cannot have more than one clustered index.

CREATE CLUSTERED COLUMNSTORE INDEX CCI_SaleKey ON dbo.SaleKey;

Msg 35372, Level 16, State 3, Line 1
You cannot create more than one clustered index on table 'dbo.SaleKey'. Consider creating a new clustered index using 'with (drop_existing = on)' option.

The second uses DROP_EXISTING with a name that matches no index. It fails with Msg 7999.

CREATE CLUSTERED COLUMNSTORE INDEX NoSuchName ON dbo.SalePlain WITH (DROP_EXISTING = ON);

Msg 7999, Level 16, State 9, Line 1
Could not find any index named 'NoSuchName' for table 'dbo.SalePlain'.

Rowstore to Columnstore for a Plain Clustered Index

When the clustered index is not a constraint, the command works. The name has to match the existing index, and here that is CX_SalePlain. SQL Server reads the whole table and writes it again as a columnstore.

CREATE CLUSTERED COLUMNSTORE INDEX CX_SalePlain ON dbo.SalePlain WITH (DROP_EXISTING = ON);

SELECT i.name AS IndexName, i.type_desc AS IndexType, SUM(ps.used_page_count) AS UsedPages
FROM sys.indexes AS i
INNER JOIN sys.dm_db_partition_stats AS ps ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.SalePlain')
GROUP BY i.name, i.type_desc;
SELECT state_description, COUNT(*) AS RowGroups, SUM(total_rows) AS TotalRows
FROM sys.column_store_row_groups
WHERE object_id = OBJECT_ID(N'dbo.SalePlain')
GROUP BY state_description;

The second query prints a warning that the join order has been enforced because a local join hint is used. The results are correct, and no action is needed.

A columnstore index stores each column in row groups. A row group holds up to 1,048,576 rows, so 600,000 rows fit in one. Small inserts that arrive later land in a small rowstore called the delta store first. SQL Server compresses them into a row group when enough have collected.

The table shrank from 2,459 pages to 296. All 600,000 rows sit in one compressed row group. This data repeats a lot, so it compresses well, and a real table saves less.

Convert a Table With a Primary Key

A primary key constraint on a table with a clustered columnstore index needs SQL Server 2016 or later. The index itself needs SQL Server 2014 or later. For the table with the key, three steps are needed. Drop the constraint, build the clustered columnstore index, and add the key back as a nonclustered constraint. Each statement is its own batch.

ALTER TABLE dbo.SaleKey DROP CONSTRAINT PK_SaleKey;
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SaleKey ON dbo.SaleKey;
GO
ALTER TABLE dbo.SaleKey ADD CONSTRAINT PK_SaleKey PRIMARY KEY NONCLUSTERED (SaleID);

If another table has a foreign key to this key, the first step fails with Msg 3725. Drop that foreign key first, and add it back at the end. Now measure each index of the table.

SELECT i.name AS IndexName, i.type_desc AS IndexType, SUM(ps.used_page_count) AS UsedPages
FROM sys.indexes AS i
INNER JOIN sys.dm_db_partition_stats AS ps ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.SaleKey')
GROUP BY i.name, i.type_desc
ORDER BY i.name;

The columnstore itself needs about 300 pages, as in the other table. The key, now a nonclustered index, needs many more. Total space for the table is larger than the compressed data. Keep the key only if something needs the uniqueness rule or a fast lookup by key. A table that is only scanned and summed can do without it.

A heap needs no steps at all. A table with no clustered index takes CREATE CLUSTERED COLUMNSTORE INDEX directly, and the rows move into the new structure. The table keeps its name, its columns and its data. Only the storage changes.

Is Columnstore Right for This Table?

You could argue that every large table should become a columnstore. Columnstore suits scans and totals over many rows. A table that mostly serves single-row lookups and updates fits rowstore better. The key you re-added is a sign of that. Test the workload on a copy first. The change rewrites the whole table, so plan time and log space for a table of real size.

What to Remember

For rowstore to columnstore, check who owns the clustered index. A plain index converts with DROP_EXISTING and its own name. A constraint needs the three steps and a nonclustered key. Compare the sizes afterward, and keep the key only when it earns its space. Run the cleanup script when you finish.

USE master;
GO
IF DB_ID(N'RowstoreSwapDemo') IS NOT NULL
BEGIN
    ALTER DATABASE RowstoreSwapDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE RowstoreSwapDemo;
END;

A clustered index is not a label you rename, it is the table you are rebuilding.

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