Why should you care?

Summing revenue across millions of fact rows is far cheaper with columnstore than rowstore indexes.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
IF OBJECT_ID(N'dbo.FactOrderDaily', N'U') IS NOT NULL DROP TABLE dbo.FactOrderDaily;
CREATE TABLE dbo.FactOrderDaily (
    TheDate DATE NOT NULL,
    ProductId INT NOT NULL,
    Qty INT NOT NULL,
    Revenue DECIMAL(18,2) NOT NULL
);
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactOrderDaily ON dbo.FactOrderDaily;
INSERT INTO dbo.FactOrderDaily (TheDate, ProductId, Qty, Revenue)
VALUES ('2026-07-01', 1, 10, 49990.00), ('2026-07-01', 2, 3, 1500.00);
SELECT TheDate, SUM(Revenue) AS DayRevenue
FROM dbo.FactOrderDaily
GROUP BY TheDate;

What happened?

• CLUSTERED COLUMNSTORE replaces rowstore storage for this fact table.
• The GROUP BY SUM benefits from batch-mode aggregation on column segments.

Practice next

1. Create FactOrderDaily with CCI.
2. Insert sample facts and aggregate.
3. Check sys.indexes type_desc = CLUSTERED COLUMNSTORE.
4. Add more days and rerun the SUM.
5. Create a nonclustered columnstore on a rowstore table (NCCI) as a hybrid.

Remember

Columnstore = analytics storage. Compresses and scans columns efficiently. Keep OLTP tables on rowstore B-trees.

Sales fact for BI

DataVerse analytics loads FactOrderDaily with CCI.

Outcome: Power BI aggregates finish in seconds, not minutes.