# Materialized view patterns, trade-offs, and when to use each on SQL Server/Azure SQL and .NET > Source: > Published: 2026-06-26 11:51:38+00:00 # What do we mean by “materialized view” on Azure SQL? SQL Server/Azure SQL doesn’t have Oracle-style materialized views; the closest native feature is an **Indexed View** (schema-bound view with a clustered index). In practice, teams use three flavors: # Summary and key points - **Materialized read models (indexed views or custom projection tables)** absolutely help large-scale pagination by **removing runtime work** and enabling **perfect indexes** for your cursor pattern. - Use **custom read models + outbox** for maximum control and minimal read latency; use **indexed views** when you want SQL Server to maintain a specific aggregation/join. - Keep **seek/cursor pagination**—it’s the core scalability lever. Materialization just makes each page cheaper. - Add **Redis page/window caching** for super-hot lists, and move faceted text search to **Azure AI Search/Elasticsearch** if needed. ## 1. Indexed View (native) - **What it is:** A `VIEW ... WITH SCHEMABINDING` + **clustered index** that stores view rows physically. - **When it shines:** Expensive joins/aggregations that are stable and heavily reused. - **Impact on pagination:** You paginate **over the view** with a covering index aligned to your sort key, so the engine skips the big base-table joins at runtime. - **Costs/constraints:** Write penalty (maintained on every insert/update), schema rules (determinism, no `*` , etc.). ## 2. Materialized Read Model Table (custom) - **What it is:** A **denormalized table** that you keep in sync (CQRS projection). Often called a “projection,” “read model,” or “summary table.” - **Sync options:** - **Streaming** via outbox + background dispatcher (near-real-time, strong control). - **CDC/Change Tracking** + ETL job (near-real-time or batch). **Impact on pagination:** You tailor the table to the exact API shape, add a **clustered index on **`(SortKey, Id)` and include only the columns needed by the list page → extremely fast keyset pagination. **Costs:** Extra storage + write/update path. You must design rebuild/backfill procedures. ## 3. Precomputed Page Cache (ephemeral) - **What it is:** Cache **page windows** (e.g., first 50, next 50 cursors) in Redis keyed by filter + sort + cursor. - **Impact:** Removes repeat read costs for hot feeds and “first page” traffic. - **Costs:** Cache invalidation; combinatorial explosion for many filter combos (use selectively). # Do they improve pagination performance? **Yes, by shrinking the query work per page**: - No/less joining at runtime. - Narrow, page-friendly rows (no wide payload). - Perfectly **aligned indexes** for your sort/filter. - Fewer logical reads, lower CPU, and better P95/P99. But remember: **seek/cursor pagination** is still required for huge data. Materialization won’t fix OFFSET/FETCH’s deep-page slowness. # Design playbook (10M+ rows) ## 1) Choose your read model - **If your list endpoint needs multiple joins, computed fields, or rollups:** Use a **custom read model table** or **indexed view**. - **If writes are heavy and latency tolerance is low (OLTP):** Prefer a **read model table** updated asynchronously (outbox/CQRS). Indexed views add write latency. ## 2) Shape for pagination - Store exactly what the endpoint needs (no N+1 lookups). - **Clustered index:** `(SortKey, Id)` in the same direction you present (often `DESC` for newest-first). - **Covering index:** If the clustered key differs (for example, you cluster by `Id` for other reasons), add a **nonclustered** index on `(SortKey, Id) INCLUDE ()` . ## 3) Keep it fresh ## 4) Paginate with a cursor (seek) - Stable order (e.g., `CreatedAtUtc DESC, Id DESC` ). - Seek predicate using last item’s `(SortKey, Id)` from a **signed **`pageToken` . - **Projection to DTO** in the query (don’t materialize entities). ## 5) Validate filters & align indexes - Whitelist filter fields. - If you commonly filter by `Status` , build `(Status, SortKey, Id)` index and **INCLUDE** the display columns. # Alternatives & complements ### A) **Indexed View** vs **Read Model Table** - **Indexed View**: Zero custom sync code; SQL Server maintains it. Great for **deterministic aggregates**. But it **taxes writes** and is harder to evolve. - **Read Model Table**: Maximum control, **cheapest reads**, and you can store denormalized JSON or precomputed projections. Needs a projector (worker) and backfill logic. ### B) **Search engine for faceted filtering** ### C) **Partitioning & storage options** - **Range partitioning** on date can keep working sets small (monthly tables or partition function). - **Columnstore** is excellent for analytics scans, but **not ideal** for cursor pagination of OLTP feeds; prefer rowstore + narrow covering indexes. ### D) **Cosmos DB** - If your data is already in Cosmos, create a **projection container** tailored to the list shape; use **SDK continuation tokens** and `ORDER BY createdAt, id` with partition-aligned queries. # When *not* to use materialization - If the base list is already a single table with a perfect covering index and no computed fields, adding a read model won’t move the needle much—the **seek query is already optimal**. - If write throughput is extreme and the projection would add unacceptable write amplification, prefer **on-the-fly** with careful indexing or consider **eventual consistency** projections for read paths that can tolerate lag. # Practical .NET implementation sketch **Outbox + projector (EF Core 9)** - In your command handler, write domain changes **and** append `OutboxEvent` in the same transaction. - A hosted service (or Function/Worker) polls unsent events and `UPSERT` s the **ReadModel_Items** table. - The list endpoint queries **ReadModel_Items** with the **seek** pattern and emits a signed `nextPageToken` . **SQL for read model** **Endpoint (pseudo)**