SQL Server Views and Indexed Views

A view is a named query. SQL Server expands it into the outer statement at optimization time, so a plain view has no storage and no performance effect by itself -- it's an abstraction layer. Indexed views are the exception: materialize a view with a unique clustered index and SQL Server maintains its result set on disk, updated synchronously with every write to the base tables. This guide covers both, including the traps: SELECT * schema drift, the WITH CHECK OPTION semantics, the long restriction list for indexed views, and the edition-dependent NOEXPAND behavior.

Creating and Altering Views

CREATE VIEW dbo.active_customers
AS
SELECT customer_id, name, region
FROM dbo.customers
WHERE is_active = 1;

Since SQL Server 2016 SP1 you can write CREATE OR ALTER VIEW, which removes the drop-vs-alter dance in deployment scripts. ALTER VIEW (either form) preserves the permissions granted on the view; DROP + CREATE loses them -- a real difference in databases where grants live on views rather than tables.

To read a view's definition back:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.active_customers'));
-- or: EXEC sp_helptext 'dbo.active_customers';

WITH ENCRYPTION obfuscates the definition. It's weak protection (well-known tooling reverses it) and it breaks replication and debugging; hard to recommend in 2026.

The SELECT * Schema-Drift Trap

A view's column list is fixed at creation time, even if the definition says SELECT *:

CREATE VIEW dbo.v_orders AS SELECT * FROM dbo.orders;
 
ALTER TABLE dbo.orders ADD discount decimal(5,2) NULL;
 
SELECT * FROM dbo.v_orders;   -- discount is NOT there

Worse than a missing column: if you drop and re-add columns in a different order, the view can return values under the wrong column names, because the view's metadata still maps ordinal positions from creation time. The fix after any base-table change:

EXEC sp_refreshview 'dbo.v_orders';

Better: never use SELECT * in a view definition. List the columns. Every base-table change then either keeps working or fails loudly at ALTER time instead of lying silently at query time.

Updatable Views and WITH CHECK OPTION

You can INSERT/UPDATE/DELETE through a view if the modification maps to exactly one base table, the view contains no aggregates, DISTINCT, GROUP BY, UNION, or derived columns in the modified column list, and all NOT NULL columns without defaults are reachable through the view.

The subtle part is what happens to rows that leave the view's filter:

CREATE VIEW dbo.north_customers AS
SELECT customer_id, name, region FROM dbo.customers
WHERE region = 'north';
 
-- succeeds by default -- and the row vanishes from the view
UPDATE dbo.north_customers SET region = 'south' WHERE customer_id = 1;

By default SQL Server happily writes rows through a view into a state the view can no longer see. WITH CHECK OPTION closes that hole:

CREATE OR ALTER VIEW dbo.north_customers AS
SELECT customer_id, name, region FROM dbo.customers
WHERE region = 'north'
WITH CHECK OPTION;
 
UPDATE dbo.north_customers SET region = 'south' WHERE customer_id = 1;
-- Msg 550: The attempted insert or update failed because the target view
-- either specifies WITH CHECK OPTION or spans a view that specifies
-- WITH CHECK OPTION...

If a view is meant as a security or partitioning boundary, you almost certainly want WITH CHECK OPTION. For anything more complex than single-table filters, INSTEAD OF triggers on the view are the escape hatch -- they receive the attempted modification and can route it to the right tables.

ORDER BY Inside a View

Not allowed, with one misleading exception:

CREATE VIEW dbo.v_sorted AS
SELECT TOP 100 PERCENT * FROM dbo.orders ORDER BY order_date DESC;  -- compiles!

This compiles because ORDER BY is permitted when TOP is present -- but TOP 100 PERCENT ... ORDER BY is a no-op the optimizer strips out entirely. The view does not return sorted rows. This old trick stopped working in SQL Server 2005 and has confused people ever since. Views produce sets; the outer query owns the sort. If you need "first N", TOP (N) ... ORDER BY inside a view is legitimate; "sorted output" is not a thing a view can promise.

Nesting and Its Cost

Views can reference views (up to 32 levels). The optimizer flattens the whole stack into one query tree, so nesting isn't inherently slow -- but five layers of views, each joining "just one more" table, routinely produce queries that touch 15 tables to return 2 columns. The optimizer eliminates some unused joins (it needs foreign keys and uniqueness guarantees to prove they're safe to remove), but not reliably through deep stacks with outer joins and filters. If a query on a nested view is slow, SET STATISTICS IO ON and check which base tables are actually being read; the fix is usually to query the base tables directly or flatten the view.

Indexed Views (Materialized Views, T-SQL Style)

SQL Server has no CREATE MATERIALIZED VIEW. The equivalent is an indexed view: create a view WITH SCHEMABINDING, then put a unique clustered index on it. From that point the result set physically exists and is maintained synchronously -- every write to a base table updates the view's index in the same transaction.

CREATE OR ALTER VIEW dbo.sales_by_region
WITH SCHEMABINDING
AS
SELECT region,
       SUM(amount)  AS total_amount,
       COUNT_BIG(*) AS row_count          -- required, see below
FROM dbo.orders                            -- two-part names required
GROUP BY region;
GO
CREATE UNIQUE CLUSTERED INDEX ix_sales_by_region
ON dbo.sales_by_region (region);

WITH SCHEMABINDING locks the base tables' schema: you can't drop dbo.orders or alter columns the view references until the view is dropped. That's the deal you're making -- the view is now part of the schema, not decoration on top of it.

The Restriction List

Indexed view definitions are heavily restricted. The ones that bite in practice:

• COUNT_BIG(*) is mandatory whenever there's a GROUP BY. It's how the engine maintains the aggregate incrementally (it needs to know when a group's count hits zero to delete the row).
• Aggregates are limited to SUM and COUNT_BIG. No MIN, MAX, AVG, COUNT, or DISTINCT. For AVG, store SUM and COUNT_BIG and divide at query time. MIN/MAX are excluded because a delete of the current min/max would force a rescan to find the next one -- incompatible with incremental maintenance.
• SUM over a nullable expression is not allowed.
• No SELECT *, no UNION, no outer joins, no self-joins, no subqueries, no derived tables, no CTEs, no TOP, no window functions.
• All tables must be referenced with two-part names (dbo.orders), same database, and the session needs specific SET options (ANSI_NULLS ON, QUOTED_IDENTIFIER ON, and five others) both at creation and for any write to the base tables afterward. A legacy app writing with the wrong SET options gets errors on its DML, not just a stale view -- this is the restriction that turns an indexed view into a production incident.
• The definition must be deterministic: no GETDATE(), no RAND(), and no expressions whose result depends on language or dateformat settings (CONVERT with certain styles fails this).

NOEXPAND and the Edition Question

Whether queries actually use your indexed view depends on edition:

• Enterprise edition: the optimizer considers indexed views automatically -- even for queries that never mention the view, if it can match the query tree against the view definition (as of SQL Server 2025's documentation this is still an Enterprise-only feature).
• Standard and other editions: the optimizer expands the view to its definition and ignores the index -- unless you reference the view explicitly with the NOEXPAND hint:

SELECT region, total_amount
FROM dbo.sales_by_region WITH (NOEXPAND);

Testing an indexed view on Standard without NOEXPAND and concluding "indexed views don't work" is a rite of passage. There's a consolation prize: with NOEXPAND, SQL Server creates and uses statistics on the view itself, exactly as on a table. Many teams on Enterprise use NOEXPAND anyway for plan stability -- automatic matching can silently stop applying after a query change.

When Indexed Views Pay Off

The write cost is real: every INSERT/UPDATE/DELETE on a base table synchronously maintains every indexed view over it, and complex indexed views can degrade DML plans badly. The profile that works: expensive aggregation over large, read-mostly tables, queried far more often than written. High-churn OLTP tables with an indexed view on top is the anti-pattern -- for that, consider a columnstore index on the base table instead (see indexes explained), or periodic summary tables refreshed on a schedule if staleness is acceptable.

Views vs Alternatives

Exploring which of a database's views are plain and which are indexed is one query away (sys.views joined to sys.indexes); Mako's AI autocomplete knows the catalog views, which helps when you don't remember the join.

Mako connects to SQL Server, PostgreSQL, MySQL, and 6 other databases with AI-powered autocomplete. Try it free at mako.ai.