SQL WORLD: Advanced Indexing Strategies in SQL Server: Filtered, Columnstore, and Indexed Views

Introduction: Why Advanced Indexing Strategies Matter

If you've been writing T-SQL queries for a while, you already know that a well-placed index can turn a painful 30-second query into a sub-second result. But standard nonclustered indexes only take you so far. When your database grows — and it always does — you need advanced indexing strategies in SQL Server to stay ahead of performance bottlenecks.

In this guide, we'll explore three powerful but often underused index types:

Filtered Indexes — precision targeting for selective queries
Columnstore Indexes — high-speed analytics over millions of rows
Indexed Views — pre-aggregated result sets that SQL Server maintains automatically

We'll use real tables from the WideWorldImporters sample database throughout, so every example is grounded in a realistic schema you can run on your own machine.

What Are Filtered Indexes — And When Should You Use One?

A filtered index is a nonclustered index with a WHERE clause baked in. Instead of indexing every row in a table, you index only the rows that match a specific condition. This makes the index smaller, faster to maintain, and dramatically more efficient for queries targeting that subset of data.

Think of it as a "specialist" index — it doesn't try to serve every query, just the ones that matter most.

Real-World Use Case: Active Orders in WideWorldImporters

In Sales.Orders, most reporting queries focus on orders that haven't been invoiced yet — open orders that need attention. Creating a filtered index on that subset is a textbook use of this technique.

-- Create a filtered index on Sales.Orders for uninvoiced orders only
CREATE NONCLUSTERED INDEX IX_Orders_OpenOrders
ON Sales.Orders (OrderDate, CustomerID)
INCLUDE (SalespersonPersonID, ExpectedDeliveryDate)
WHERE IsUndersupplyBackordered = 0
  AND PickingCompletedWhen IS NULL;

Now, when your sales team runs a query like this:

-- Query to retrieve all open, unshipped orders
SELECT
    o.OrderID,
    o.OrderDate,
    o.ExpectedDeliveryDate,
    c.CustomerName,
    p.FullName AS Salesperson
FROM Sales.Orders o
JOIN Sales.Customers c ON c.CustomerID = o.CustomerID
JOIN Application.People p ON p.PersonID = o.SalespersonPersonID
WHERE o.IsUndersupplyBackordered = 0
  AND o.PickingCompletedWhen IS NULL
ORDER BY o.OrderDate DESC;

SQL Server will use the filtered index instead of scanning the full Sales.Orders table. Because the index only covers the rows matching the filter condition, it's a fraction of the full table size — meaning fewer pages read, less I/O, and faster results.

Developer Insight: Filtered indexes shine when a meaningful portion of your table is "inactive" data — historical records, completed transactions, archived rows. Index only what queries actually touch.

Columnstore Indexes: Turbocharging Analytical Queries

A columnstore index flips the traditional storage model on its head. Instead of storing rows together (the default rowstore format), it stores each column separately and compresses the data aggressively. The result? Analytical queries that scan millions of rows and perform aggregations can run 10 to 100 times faster compared to traditional indexes.

There are two types:

Clustered Columnstore Index (CCI) — replaces the table's primary storage format. Ideal for pure analytics tables.
Nonclustered Columnstore Index (NCCI) — added on top of an existing rowstore table. Perfect for mixed OLTP/analytics workloads.

Example: Analyzing Sales by Stock Item in WideWorldImporters

The Sales.OrderLines table holds all line-item detail for every order. For reporting purposes — revenue summaries, top products, seasonal trends — a columnstore index is a natural fit.

-- Add a nonclustered columnstore index to Sales.OrderLines
-- for fast aggregation queries
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_OrderLines_Analytics
ON Sales.OrderLines
(
    StockItemID,
    OrderID,
    Quantity,
    UnitPrice,
    TaxRate,
    PickedQuantity
);

With this index in place, a monthly revenue report like this one becomes dramatically faster:

-- Monthly revenue summary by stock item
SELECT
    si.StockItemName,
    YEAR(o.OrderDate)   AS OrderYear,
    MONTH(o.OrderDate)  AS OrderMonth,
    SUM(ol.Quantity)        AS TotalQuantity,
    SUM(ol.Quantity * ol.UnitPrice) AS TotalRevenue
FROM Sales.OrderLines ol
JOIN Sales.Orders o          ON o.OrderID    = ol.OrderID
JOIN Warehouse.StockItems si ON si.StockItemID = ol.StockItemID
GROUP BY
    si.StockItemName,
    YEAR(o.OrderDate),
    MONTH(o.OrderDate)
ORDER BY
    OrderYear DESC,
    OrderMonth DESC,
    TotalRevenue DESC;

SQL Server's batch mode execution engine processes the columnstore data in large batches (typically 900+ rows at a time), instead of one row at a time. This is why the performance gains on aggregation-heavy queries are so significant.

When to Use a Clustered Columnstore Index

If you have a reporting or data warehouse table that is rarely updated row-by-row (mostly bulk-loaded), a Clustered Columnstore Index gives you maximum compression and speed:

-- Example: A dedicated order summary table built for analytics
-- Create it fresh with CCI as the primary storage
CREATE TABLE Sales.OrderLineSummary
(
    OrderID         INT            NOT NULL,
    StockItemID     INT            NOT NULL,
    CustomerID      INT            NOT NULL,
    OrderDate       DATE           NOT NULL,
    Quantity        INT            NOT NULL,
    UnitPrice       DECIMAL(18,2)  NOT NULL,
    TaxRate         DECIMAL(3,2)   NOT NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX CCI_OrderLineSummary
ON Sales.OrderLineSummary;

Developer Insight: Don't add a columnstore index to a small table hoping for a miracle. The gains kick in at scale — think hundreds of thousands to millions of rows. On smaller tables, a well-designed nonclustered rowstore index is usually the better choice.

Indexed Views: Pre-Computed Aggregations SQL Server Maintains For You

An indexed view (also called a materialized view in other databases) takes a regular view and physically stores its result set on disk — just like a table. SQL Server then keeps that stored result synchronized with the underlying tables automatically.

This is incredibly powerful for complex aggregations or joins that run frequently. Instead of recalculating the same expensive query hundreds of times a day, SQL Server reads from the pre-built result set.

Key Requirements for Indexed Views

Before you can add a clustered index to a view, SQL Server enforces strict rules:

The view must be created with WITH SCHEMABINDING
It cannot use SELECT *, DISTINCT, TOP, subqueries, outer joins, or non-deterministic functions
All referenced tables must be two-part named (e.g., Sales.Orders, not just Orders)
Aggregates like SUM() must also include COUNT_BIG(*)

Example: Stock Holdings Summary Indexed View

Here's a practical indexed view combining Warehouse.StockItems and Warehouse.StockItemHoldings to produce a fast, always-current inventory snapshot:

-- Step 1: Create the view with SCHEMABINDING
CREATE VIEW Warehouse.vw_StockInventorySummary
WITH SCHEMABINDING
AS
SELECT
    si.StockItemID,
    si.StockItemName,
    si.SupplierID,
    sih.QuantityOnHand,
    sih.ReorderLevel,
    sih.TargetStockLevel,
    COUNT_BIG(*) AS RowCount
FROM Warehouse.StockItems si
JOIN Warehouse.StockItemHoldings sih
    ON sih.StockItemID = si.StockItemID
GROUP BY
    si.StockItemID,
    si.StockItemName,
    si.SupplierID,
    sih.QuantityOnHand,
    sih.ReorderLevel,
    sih.TargetStockLevel;
GO

-- Step 2: Create a unique clustered index to materialize the view
CREATE UNIQUE CLUSTERED INDEX UCX_vw_StockInventorySummary
ON Warehouse.vw_StockInventorySummary (StockItemID);
GO

Now when you query the view directly (or when the query optimizer detects a match), SQL Server reads the pre-materialized data instead of re-joining and re-aggregating on every call:

-- Query the indexed view directly -- reads pre-computed data
SELECT
    StockItemName,
    SupplierID,
    QuantityOnHand,
    ReorderLevel
FROM Warehouse.vw_StockInventorySummary
WHERE QuantityOnHand < ReorderLevel
ORDER BY QuantityOnHand ASC;

Developer Insight: Indexed views are a write-time investment. Every INSERT, UPDATE, or DELETE on the base tables triggers SQL Server to update the view's stored data. Use indexed views on tables with heavy reads and moderate writes — not on tables that change thousands of times per minute.

Tips and Common Mistakes to Avoid

✅ Do This

Test with execution plans. Always use the Actual Execution Plan (Ctrl + M in SSMS) to verify your index is being used.
Use INCLUDE columns wisely. Adding non-key columns to a filtered or nonclustered index can eliminate key lookups:

-- Covering index for supplier lookup queries
CREATE NONCLUSTERED INDEX IX_Suppliers_Category
ON Purchasing.Suppliers (SupplierCategoryID)
INCLUDE (SupplierName, PhoneNumber, WebsiteURL)
WHERE IsRetired = 0;

Maintain your indexes. Columnstore indexes accumulate deleted rows in compressed rowgroups over time. Periodically run ALTER INDEX ... REORGANIZE to merge delta rowgroups and reclaim space.

❌ Avoid These Mistakes

Over-indexing. Every index adds overhead on INSERT, UPDATE, and DELETE. A table with 15 indexes on it will have fast reads and slow writes. Audit your indexes regularly using sys.dm_db_index_usage_stats.
Filtered index filter mismatch. If your query's WHERE clause doesn't exactly match the filtered index predicate, SQL Server will ignore the index entirely. Be precise.
Adding columnstore to high-churn OLTP tables. Clustered columnstore indexes struggle with frequent single-row inserts. Use a nonclustered columnstore index on such tables instead.
Forgetting WITH (NOEXPAND) hint on Standard Edition. On SQL Server Standard Edition, indexed views are not automatically matched by the optimizer — you need to add the WITH (NOEXPAND) hint explicitly:

SELECT StockItemName, QuantityOnHand
FROM Warehouse.vw_StockInventorySummary WITH (NOEXPAND)
WHERE QuantityOnHand < ReorderLevel;

Summary / Key Takeaways

Advanced indexing in SQL Server is about matching the right tool to the right problem:

Filtered Indexes reduce index size and maintenance cost by targeting only the rows your queries actually need. Perfect for active-record patterns and selective status flags.
Columnstore Indexes (especially nonclustered) are the go-to choice for analytics and reporting queries that aggregate large datasets. They work especially well alongside existing rowstore tables in mixed workloads.
Indexed Views pre-compute expensive joins and aggregations, storing the result on disk for near-instant retrieval. Best suited for read-heavy reporting with moderate write activity.

None of these index types is a silver bullet. The best strategy is always to profile your actual query workload, look at execution plans, and apply targeted solutions — not blanket indexing across every column.

FAQ: Beginner-Friendly Questions

Q1: Can I use a filtered index and a columnstore index on the same table?

Yes, absolutely. A table can have multiple index types simultaneously. For example, Sales.OrderLines could have a nonclustered columnstore index for reporting queries and a filtered nonclustered rowstore index for operational queries on recent or open orders. SQL Server's query optimizer chooses the best index per query automatically.

Q2: Do indexed views update automatically when the base table changes?

Yes. SQL Server maintains indexed views synchronously — whenever the underlying base tables are modified, the indexed view's stored data is updated in the same transaction. This keeps the data always consistent, but it does mean write operations carry a small additional cost.

Q3: How do I know if a filtered index is actually being used by SQL Server?

Check the actual execution plan in SSMS after running your query. If the index is being used, you'll see an "Index Seek" operation referencing your filtered index name. You can also query sys.dm_db_index_usage_stats to see how many seeks, scans, and lookups each index has accumulated since the last server restart.

All SQL examples in this post use the WideWorldImporters sample database, available free from Microsoft on GitHub. Test these techniques in a development environment before applying them to production systems.

Advanced Indexing Strategies in SQL Server: Filtered, Columnstore, and Indexed Views