⚡ Materialized Views in SQL Server
Complex aggregation query taking 10 seconds? Create indexed view. Query becomes instant. Data stays fresh automatically.
Problem: Slow Aggregate Query
-- This runs every time, slow on large tables
SELECT
CategoryId,
COUNT(*) AS ProductCount,
AVG(Price) AS AvgPrice,
SUM(Stock) AS TotalStock
FROM Products
GROUP BY CategoryId;
-- 10 seconds on 10M rows
Solution: Indexed View
-- Step 1: Create view with SCHEMABINDING
CREATE VIEW dbo.ProductStats
WITH SCHEMABINDING
AS
SELECT
CategoryId,
COUNT_BIG(*) AS ProductCount,
SUM(Price) AS TotalPrice,
SUM(Stock) AS TotalStock
FROM dbo.Products
GROUP BY CategoryId;
GO
-- Step 2: Create clustered index
CREATE UNIQUE CLUSTERED INDEX IX_ProductStats
ON dbo.ProductStats(CategoryId);
GO
-- Step 3: Query it like a table
SELECT * FROM ProductStats;
-- Now: < 0.1 seconds!
🎯 How It Works
- Materialized: Results stored physically on disk
- Auto-updated: When base tables change, view updates
- Indexed: Query optimizer uses indexes
- Transparent: Apps don't know it's a view
⚠️ Requirements
- WITH SCHEMABINDING required
- Must use COUNT_BIG not COUNT
- No OUTER joins (INNER only)
- No subqueries in SELECT
- First index must be UNIQUE CLUSTERED
💡 When to Use
- Heavy aggregations (COUNT, SUM, AVG)
- Complex joins queried frequently
- Reporting queries on large tables
- Dashboard queries that don't change much
"Dashboard aggregation query took 12 seconds. Created indexed view. Now instant. Reports load in real-time. Users think we upgraded servers."