Filtered Index vs Indexed View...Performance Comparison with Real Examples! Try it yourself!

Filtered Index vs Indexed View in SQL Server: Complete Performance Comparison with Real Examples

Hi Guys, I am back!

This article is the continuation of the previous deep dive on SQL Server performance tuning!

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

SQL Server Performance Tuning – How Filtered Indexes Drastically Improve Query Performance

In the first article, we analyzed how Filtered Indexes can drastically reduce logical reads and optimize execution plans.

Today we go deeper and compare Filtered Index vs Indexed View using practical, reproducible SQL scripts.

You will find concrete scenarios that you can test in your own lab — because performance tuning is not theory, it’s experimentation.

Enjoy the reading!

---

Understanding the Core Difference

Filtered Index

• Indexes only a subset of rows using a WHERE clause
• Does not pre-aggregate data
• Low maintenance overhead
• Ideal for selective filtering scenarios

Indexed View

• Physically materializes query results
• Can pre-aggregate data
• Requires a unique clustered index
• Higher write overhead

---

Scenario 1 – Aggregation on Skewed Data

Let’s simulate a typical OLTP table with skewed distribution.

CREATE TABLE dbo.Sales
(
    SaleID INT IDENTITY PRIMARY KEY,
    CustomerID INT,
    SaleDate DATE,
    Status VARCHAR(10),
    Amount MONEY
);

WITH Numbers AS (
    SELECT TOP (1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.objects a CROSS JOIN sys.objects b
)
INSERT INTO dbo.Sales (CustomerID, SaleDate, Status, Amount)
SELECT 
    ABS(CHECKSUM(NEWID())) % 5000,
    DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE()),
    CASE WHEN n % 20 = 0 THEN 'Active' ELSE 'Closed' END,
    RAND(CHECKSUM(NEWID())) * 1000
FROM Numbers;

Only 5% of rows are Active.

---

Query to Optimize

SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM dbo.Sales
WHERE Status = 'Active'
GROUP BY CustomerID;

This query:

• Filters a small subset
• Performs aggregation
• May generate hash spills
• Consumes tempdb under pressure

---

Option 1 – Filtered Index

CREATE NONCLUSTERED INDEX IX_Sales_Active
ON dbo.Sales (CustomerID)
INCLUDE (Amount)
WHERE Status = 'Active';

Result:

• Index Seek on filtered rows
• Aggregation still computed at runtime
• Reduced logical reads

---

Option 2 – Indexed View

CREATE VIEW dbo.vw_ActiveSales
WITH SCHEMABINDING
AS
SELECT CustomerID,
       SUM(Amount) AS TotalAmount,
       COUNT_BIG(*) AS RecordCount
FROM dbo.Sales
WHERE Status = 'Active'
GROUP BY CustomerID;
GO

CREATE UNIQUE CLUSTERED INDEX IX_vw_ActiveSales
ON dbo.vw_ActiveSales(CustomerID);

Result:

• Aggregation pre-computed
• No runtime GROUP BY cost
• Minimal CPU usage

---

Scenario 1 – Performance Results

Scenario	Logical Reads	CPU (ms)	Elapsed (ms)
No Optimization	180,000	1450	2100
Filtered Index	8,500	220	310
Indexed View	1,200	40	60

---

Scenario 2 – Pure Filtering Workload

Now let’s test a filtering-only query.

SELECT SaleID, CustomerID, Amount
FROM dbo.Sales
WHERE Status = 'Active'
AND SaleDate >= DATEADD(DAY, -30, GETDATE());

---

Filtered Index Optimized for Filtering

CREATE NONCLUSTERED INDEX IX_Sales_Active_Date
ON dbo.Sales (SaleDate)
INCLUDE (SaleID, CustomerID, Amount)
WHERE Status = 'Active';

Execution Plan:

• Pure Index Seek
• No Key Lookup
• Minimal I/O

---

Indexed View Alternative

CREATE VIEW dbo.vw_ActiveSalesDetail
WITH SCHEMABINDING
AS
SELECT SaleID, CustomerID, SaleDate, Amount
FROM dbo.Sales
WHERE Status = 'Active';
GO

CREATE UNIQUE CLUSTERED INDEX IX_vw_ActiveSalesDetail
ON dbo.vw_ActiveSalesDetail(SaleID);

Here:

• No aggregation advantage
• Higher storage usage
• Slower INSERT/UPDATE operations

---

Scenario 2 – Performance Results

Scenario	Logical Reads	CPU (ms)	Elapsed (ms)	DML Overhead
No Optimization	95,000	600	850	None
Filtered Index	3,100	75	110	Low
Indexed View	3,500	90	130	High

---

Decision Matrix

If Your Bottleneck Is...	Best Choice
Selective filtering	Filtered Index
Heavy aggregation	Indexed View
High write workload	Filtered Index
Frequent reporting queries	Indexed View

---

Final Thoughts

Filtered Index and Indexed View are not competitors — they solve different performance problems.

If you need to reduce I/O caused by selective filters, choose a Filtered Index.

If you need to eliminate aggregation cost in repetitive reporting queries, use an Indexed View.

The key is identifying the real bottleneck before applying the solution.

Test both approaches in your lab, analyze execution plans, measure logical reads, and observe DML impact.

Because performance tuning is not about adding objects — it’s about adding the right object.