Adding clustered columnstore index on existing table
CREATE CLUSTERED COLUMNSTORE INDEX enables you to organize a table in column format:
DROP TABLE IF EXISTS Product GO CREATE TABLE Product ( Name nvarchar(50) NOT NULL, Color nvarchar(15), Size nvarchar(5) NULL, Price money NOT NULL, Quantity int ) GO CREATE CLUSTERED COLUMNSTORE INDEX cci ON Product
Rebuild CLUSTERED COLUMNSTORE index
Clustered column store index can be rebuilt if you have a lot of deleted rows:
ALTER INDEX cci ON Products REBUILD PARTITION = ALL
Rebuilding CLUSTERED COLUMNSTORE will "reload" data from the current table into new one and apply compression again, remove deleted rows, etc.
You can rebuild one or more partitions.
Table with CLUSTERED COLUMNSTORE index
If you want to have a table organized in column-store format instead of row store, add INDEX cci CLUSTERED COLUMNSTORE in definition of table:
DROP TABLE IF EXISTS Product GO CREATE TABLE Product ( ProductID int, Name nvarchar(50) NOT NULL, Color nvarchar(15), Size nvarchar(5) NULL, Price money NOT NULL, Quantity int, INDEX cci CLUSTERED COLUMNSTORE )
COLUMSTORE tables are better for tables where you expect full scans and reports, while row store tables are better for tables where you will read or update smaller sets of rows.