SQLMentor // learn sql server

Views & Indexed Views

A view is a stored SELECT statement that behaves like a virtual table. Views simplify complex queries, abstract schema details from callers, and provide a security boundary. Indexed views materialise the result set on disk for dramatic read performance — at the cost of strict requirements.

Regular Views

CREATE OR ALTER VIEW hr.v_emp_directory AS
SELECT  e.employee_id,
        e.first_name + N' ' + e.last_name AS full_name,
        e.email,
        d.department_name,
        l.city,
        l.country_id
FROM    hr.employees   e
LEFT JOIN hr.departments d ON d.department_id = e.department_id
LEFT JOIN hr.locations   l ON l.location_id  = d.location_id;
GO

SELECT * FROM hr.v_emp_directory WHERE country_id = 'US';

The view itself stores no data — every query against it expands to the underlying SELECT. The optimiser is free to fold the view body into the calling query.

Updatable Views

A view is updatable if T-SQL can map an INSERT, UPDATE, or DELETE unambiguously back to rows in one base table. Rules:

  • The view must reference exactly one base table (or use INSTEAD OF triggers — see below).
  • Cannot use DISTINCT, GROUP BY, HAVING, aggregates, UNION, or TOP.
  • Cannot include set operators (UNION, INTERSECT, EXCEPT).
  • All NOT NULL columns without defaults must be in the view (for INSERT).
  • WITH CHECK OPTION makes inserts/updates that would no longer match the view's WHERE clause fail.
CREATE OR ALTER VIEW hr.v_high_earners AS
SELECT  employee_id, first_name, last_name, salary
FROM    hr.employees
WHERE   salary > 10000
WITH CHECK OPTION;
GO

-- Allowed: inserts a row that satisfies the WHERE
INSERT INTO hr.v_high_earners (employee_id, first_name, last_name, salary)
VALUES (9999, 'New', 'Hire', 12000);

-- Fails: would not satisfy salary > 10000
UPDATE hr.v_high_earners SET salary = 5000 WHERE employee_id = 9999;

For multi-table views, use an INSTEAD OF trigger to translate DML to base tables (see the Triggers chapter).

WITH SCHEMABINDING

WITH SCHEMABINDING binds the view to its referenced objects — they cannot be dropped or have their schema altered while the view depends on them. Required for indexed views.

CREATE OR ALTER VIEW hr.v_dept_summary
WITH SCHEMABINDING
AS
SELECT  d.department_id,
        d.department_name,
        COUNT_BIG(*)        AS staff_count,
        SUM(e.salary)       AS total_payroll
FROM    hr.employees   e
JOIN    hr.departments d ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
GO

Note: schemabound views must use two-part names (hr.employees, not just employees) for every referenced table.

Indexed Views (Materialized Views)

In SQL Server, a view becomes "materialised" by creating a unique clustered index on it. The result set is then physically stored on disk and maintained automatically when underlying tables change.

-- Step 1: SCHEMABINDING view, two-part names, COUNT_BIG(*)
CREATE OR ALTER VIEW hr.v_dept_summary
WITH SCHEMABINDING
AS
SELECT  d.department_id,
        d.department_name,
        COUNT_BIG(*)         AS staff_count,
        SUM(e.salary)        AS total_payroll
FROM    hr.employees   e
JOIN    hr.departments d ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
GO

-- Step 2: create unique clustered index → materialises the view
CREATE UNIQUE CLUSTERED INDEX UX_v_dept_summary_pk
    ON hr.v_dept_summary (department_id);

-- (Optional) Add nonclustered indexes
CREATE NONCLUSTERED INDEX IX_v_dept_summary_payroll
    ON hr.v_dept_summary (total_payroll);

Strict Requirements

Indexed views are powerful but have a long list of rules. The view must:

  • Be created WITH SCHEMABINDING.
  • Use two-part names for all referenced tables.
  • Reference only deterministic functions and expressions.
  • Not use:
    • Outer joins (LEFT/RIGHT/FULL JOIN)
    • Subqueries, CTEs, derived tables
    • DISTINCT, TOP, UNION, INTERSECT, EXCEPT
    • MIN/MAX/STDEV/VAR (aggregates other than SUM/COUNT_BIG)
    • Self-joins or table variables
    • OUTER APPLY / CROSS APPLY
    • Float/text/ntext/image data types in the key
  • If the view contains GROUP BY, it must include COUNT_BIG(*) (so the engine can incrementally maintain row counts).
  • ANSI_NULLS, QUOTED_IDENTIFIER, ARITHABORT, etc. must be set when the view AND the index are created.
-- Required session settings before defining indexed views
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS,
    ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;

How the Optimiser Uses Them

In Enterprise / Developer / Azure SQL DB editions, the optimiser may automatically rewrite queries to use a matching indexed view, even if the query targets the base tables. In Standard edition, you must reference the view explicitly with the NOEXPAND hint:

-- Standard edition: hint required to use the materialised data
SELECT  department_name, staff_count, total_payroll
FROM    hr.v_dept_summary WITH (NOEXPAND);

-- Enterprise: the optimiser may match this query to the indexed view
SELECT  d.department_name, COUNT_BIG(*), SUM(e.salary)
FROM    hr.employees   e
JOIN    hr.departments d ON d.department_id = e.department_id
GROUP BY d.department_name;

NOEXPAND is also a good idea in Enterprise — it removes the optimiser's choice and forces use of the index, which is usually what you want.

Maintenance Cost

Every INSERT/UPDATE/DELETE against the underlying tables must update the indexed view's clustered index. This adds DML overhead — sometimes considerably. Indexed views are best for:

  • Read-heavy reporting / dashboards over large tables
  • Pre-aggregated SUM/COUNT_BIG totals that callers query repeatedly
  • Joins of moderately-sized dimension tables to large fact tables

They are not suited to high-write OLTP tables where the maintenance overhead exceeds the read benefit.

Inspecting Views

-- View definition
EXEC sp_helptext 'hr.v_emp_directory';

-- Find indexed views
SELECT  v.name           AS view_name,
        i.name           AS index_name,
        i.type_desc
FROM    sys.views     v
JOIN    sys.indexes   i ON i.object_id = v.object_id
WHERE   i.index_id > 0;

-- Check if a view is schemabound
SELECT  name, OBJECTPROPERTYEX(object_id, 'IsSchemaBound') AS is_schema_bound
FROM    sys.views;

Best Practices

  • Use regular views to abstract complex joins and centralise filters.
  • Add WITH SCHEMABINDING whenever the view is stable — it documents dependencies and protects against accidental schema changes.
  • Reach for indexed views only when read benefit clearly outweighs DML cost; profile both sides.
  • In Standard edition (or for guaranteed plan reuse), reference indexed views with NOEXPAND.
  • For multi-table updatable views, use INSTEAD OF triggers rather than fighting view rules.

Summary

  • Views are stored SELECTs; the optimiser typically expands them inline at query time.
  • Updatable views require single-base-table mapping; multi-table updates need INSTEAD OF triggers.
  • WITH SCHEMABINDING locks dependencies and is a prerequisite for indexed views.
  • Indexed views materialise the result with a unique clustered index — strict rules: deterministic, no outer joins, COUNT_BIG(*) if grouped.
  • Use NOEXPAND in Standard edition (or for predictable plans) to force indexed-view use.