Materialized view patterns, trade-offs, and when to use each on SQL Server/Azure SQL and .NET A developer outlines three materialized view patterns for SQL Server/Azure SQL and .NET: indexed views, custom read model tables, and precomputed page caches. The post explains how each pattern improves pagination performance by reducing runtime joins and enabling perfect index alignment for cursor-based pagination. A design playbook for 10M+ rows recommends custom read model tables for heavy writes and indexed views for stable aggregations. 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