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 (<DTO columns>).

3) Keep it fresh

• 
  

  Outbox + projector (recommended):

  
  
  
  
  
  • On each domain write, append to Outbox.
  
  
  • A background worker consumes and upserts the read model.
  
  
  • Great observability and backpressure; deterministic.
  
  
  
  


• 
  

  CDC/Change Tracking:

  
  
  
  
  
  • Poll changes; apply upserts.
  
  
  • Simpler to add later; a bit more laggy.
  
  
  
  

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

• 
  

  For free-text + many facets, push to Azure AI Search/Elasticsearch.

  
  
  
  
  
  • Use search cursors (continuation tokens) and rely on their inverted indexes.
  
  
  • Keep SQL for exact transactional lists; use search for discovery/exploration.
  
  
  
  

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)

1. In your command handler, write domain changes and append OutboxEvent in the same transaction.


2. A hosted service (or Function/Worker) polls unsent events and UPSERTs the ReadModel_Items table.


3. The list endpoint queries ReadModel_Items with the seek pattern and emits a signed nextPageToken.

SQL for read model

CREATE TABLE ReadModel_Items

(

  Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,

  CreatedAtUtc DATETIME2(3) NOT NULL,

  Status TINYINT NOT NULL,

  Name NVARCHAR(200) NOT NULL,

  -- ...other columns the DTO needs only

  -- optional: a JSON column for flexible metadata

);



-- Fast seek for newest-first

CREATE UNIQUE CLUSTERED INDEX CIX_ReadModel_Items_Created_Id

ON ReadModel_Items (CreatedAtUtc DESC, Id DESC);



-- Common filter: by Status

CREATE INDEX IX_ReadModel_Items_Status_Created_Id

ON ReadModel_Items (Status, CreatedAtUtc DESC, Id DESC)

INCLUDE (Name /*, other DTO columns */);

Endpoint (pseudo)

// Decode pageToken -> (lastCreatedAtUtc, lastId)

var q = db.ReadModelItems.AsNoTracking();



q = q.OrderByDescending(x => x.CreatedAtUtc).ThenByDescending(x => x.Id);



if (cursor != null)

    q = q.Where(x => x.CreatedAtUtc < cursor.CreatedAtUtc

                  || (x.CreatedAtUtc == cursor.CreatedAtUtc && x.Id < cursor.Id));



if (statusFilter is not null)

    q = q.Where(x => x.Status == statusFilter);



var items = await q

    .Select(x => new ItemDto(x.Id, x.Name, x.Status, x.CreatedAtUtc))

    .Take(pageSize)

    .ToListAsync(ct);



// Emit nextPageToken from last item’s keys (signed)