SQL Server, Filtered Index vs Indexed View vs Columnstore Index! Part 3

Hi guys, I’m back again! 👋

In the previous articles we compared Filtered Indexes and Indexed Views, understanding when each one shines.

If you missed Part 2, you can read it here:

👉 Filtered Index vs Indexed View – Complete Performance Comparison

Today we add a new player to the game.

Because when data volume starts growing… when millions of rows become tens or hundreds of millions… we gain a powerful new ally:

Columnstore Indexes.

And this changes everything.

---

When Rowstore Is Not Enough

Until now, we worked with traditional rowstore structures:

• Filtered Index → reduce I/O for selective predicates
• Indexed View → precompute aggregations

Both are extremely powerful in OLTP scenarios. But when datasets increase significantly, row-by-row processing becomes the bottleneck.

This is where Columnstore comes in.

---

What Makes Columnstore Different?

Traditional indexes store data row by row (B-Tree structure).

Columnstore:

• Stores data by column
• Applies heavy compression
• Enables Batch Mode execution
• Processes data in vectors instead of single rows
• Dramatically improves large aggregations

There are two main types:

• Clustered Columnstore Index (CCI) → replaces table storage
• Nonclustered Columnstore Index (NCCI) → added on top of rowstore tables

Columnstore is designed for reporting workloads, data warehouse scenarios, large scans, and analytical queries over millions of rows.

---

Scenario 3 – Large-Scale Aggregation

Let’s reuse our dbo.Sales table with 1M rows. Now imagine a reporting query executed frequently:

SELECT 
    YEAR(SaleDate) AS SaleYear,
    MONTH(SaleDate) AS SaleMonth,
    SUM(Amount) AS TotalAmount,
    COUNT(*) AS TotalSales
FROM dbo.Sales
GROUP BY 
    YEAR(SaleDate),
    MONTH(SaleDate);

This query scans the full table, aggregates everything, consumes CPU in row mode, and may request large memory grants.

---

Option 1 – Filtered Index

Here, it provides no benefit. There is no selective filter and no reduced subset. Filtered indexes are not built for full-table analytics.

---

Option 2 – Indexed View

CREATE VIEW dbo.vw_MonthlySales
WITH SCHEMABINDING
AS
SELECT 
    YEAR(SaleDate) AS SaleYear,
    MONTH(SaleDate) AS SaleMonth,
    SUM(Amount) AS TotalAmount,
    COUNT_BIG(*) AS TotalSales
FROM dbo.Sales
GROUP BY 
    YEAR(SaleDate),
    MONTH(SaleDate);
GO

CREATE UNIQUE CLUSTERED INDEX IX_vw_MonthlySales
ON dbo.vw_MonthlySales (SaleYear, SaleMonth);

Result:

• Aggregation precomputed
• Minimal runtime CPU
• Extremely fast SELECT

But with heavy DML overhead, extra storage, and schema binding restrictions.

---

Option 3 – Nonclustered Columnstore Index

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Sales_Columnstore
ON dbo.Sales (SaleDate, Amount);

Execution plan switches to:

• Batch Mode
• Column elimination
• Vectorized aggregation
• Highly compressed segments

Instead of processing row by row, SQL Server processes thousands of rows per batch. That is the game changer.

---

Final Thoughts

Filtered Index, Indexed View, and Columnstore are not competitors. They solve different performance problems.

When data grows, complexity grows. But fortunately, when data grows…

we also gain a powerful ally: Columnstore.

And in large-scale systems, that ally can make the difference between minutes and milliseconds.

See you in the next deep dive 😉