How to Create a Materialized View in SQL Server

How do I create and use a materialized view in SQL Server?

A materialized view (indexed view) stores the pre-computed result set of a SELECT query on disk, letting SQL Server serve reads instantly without re-executing the query.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What problem does a materialized view solve?

Materialized views persist aggregated or join-heavy query results. SQL Server answers repeated analytics queries in milliseconds because data is already calculated and indexed.

How do I create a materialized view in SQL Server?

Use CREATE VIEW ... WITH SCHEMABINDING to lock schema, then add a unique clustered index.In Azure SQL Synapse or SQL Server 2022 you can use the native CREATE MATERIALIZED VIEW command.

Which options and parameters matter most?

SCHEMABINDING prevents underlying table changes, CLUSTERED INDEX materializes storage, and WITH (NOEXPAND) tells the optimizer to read the index directly.

Required steps for on-prem SQL Server

Ensure the SELECT is deterministic and references only schema-bound objects.
Add a COUNT_BIG(*) column if you GROUP BY.
Create a UNIQUE clustered index on the view.

How do I refresh or keep it up-to-date?

SQL Server auto-maintains indexed views during DML on base tables.For bulk loads, enable SET INDEXES ON or drop and recreate the index after loading.

What is a real-world ecommerce example?

CREATE VIEW dbo.v_daily_customer_sales WITH SCHEMABINDING AS SELECT o.customer_id, CAST(o.order_date AS date) AS sales_day, SUM(o.total_amount) AS daily_sales, COUNT_BIG(*) AS order_count FROM dbo.Orders AS o GROUP BY o.customer_id, CAST(o.order_date AS date); GO -- Materialize it CREATE UNIQUE CLUSTERED INDEX IX_v_daily_customer_sales ON dbo.v_daily_customer_sales(customer_id, sales_day);

Best practices for performance

Include only deterministic expressions.
Keep SELECT list minimal; every column is stored.
Use NOEXPAND in queries or set the database option to treat all indexed views that way.

Common pitfalls and fixes

Non-deterministic functions: GETDATE() blocks materialization.Replace with persisted column or parameter.

Missing unique clustered index: Without it, the view stays virtual. Always create the index after the view.

How do I query the materialized view?

Query it like a table: SELECT * FROM dbo.v_daily_customer_sales WITH (NOEXPAND);

Why How to Create a Materialized View in SQL Server is important

How to Create a Materialized View in SQL Server Example Usage


-- List customers with lifetime value over $5,000 using the materialized view
SELECT  c.id,
        c.name,
        v.lifetime_value
FROM    Customers c
JOIN    dbo.v_customer_totals v ON v.customer_id = c.id
WHERE   v.lifetime_value > 5000
ORDER BY v.lifetime_value DESC;

How to Create a Materialized View in SQL Server Syntax


-- Native syntax (SQL Server 2022 / Azure Synapse)
CREATE MATERIALIZED VIEW view_name
WITH (DISTRIBUTION = HASH(customer_id), CLUSTERED COLUMNSTORE INDEX)
AS
SELECT  o.customer_id,
        SUM(oi.quantity * p.price) AS total_spent
FROM    Orders       o
JOIN    OrderItems   oi ON oi.order_id = o.id
JOIN    Products     p  ON p.id       = oi.product_id
GROUP BY o.customer_id;

-- Classic indexed-view approach (all SQL Server editions)
CREATE VIEW dbo.v_customer_totals
WITH SCHEMABINDING AS
SELECT  o.customer_id,
        SUM(o.total_amount) AS lifetime_value,
        COUNT_BIG(*)        AS order_count
FROM    dbo.Orders AS o
GROUP BY o.customer_id;
GO
CREATE UNIQUE CLUSTERED INDEX IX_v_customer_totals
ON dbo.v_customer_totals(customer_id);

Common Mistakes

Skipping the unique clustered index—without it the view is never materialized. Fix: always add a unique clustered index after creating the view.
Using non-deterministic functions like GETDATE() in the SELECT list. Fix: remove or replace them with deterministic computed columns.