Views, Indexed Views, SCHEMABINDING, Updatability, and Abstraction Costs

Learning outcomes

ServiceHub wants to publish a stable reporting surface without giving every analyst knowledge of every base-table column. A developer proposes a view and tells the team that “views cache the query result.” Another developer wants to index every view because “materialized is faster.” Both statements confuse three distinct things: a normal view definition, a schema-bound view, and an indexed view whose first index physically stores rows.

1. A normal view stores a definition, not a result set

USE master;GOIF DB_ID(N'ServiceHubProgrammabilityLab') IS NULL  CREATE DATABASE ServiceHubProgrammabilityLab;GOALTER DATABASE ServiceHubProgrammabilityLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubProgrammabilityLab;GOIF SCHEMA_ID(N'ops') IS NULL EXEC(N'CREATE SCHEMA ops AUTHORIZATION dbo;');IF SCHEMA_ID(N'api') IS NULL EXEC(N'CREATE SCHEMA api AUTHORIZATION dbo;');IF SCHEMA_ID(N'lab13') IS NULL EXEC(N'CREATE SCHEMA lab13 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'ops.WorkOrder',N'U') IS NULLBEGIN  CREATE TABLE ops.WorkOrder  (    work_order_id bigint IDENTITY(1001,1) NOT NULL      CONSTRAINT PK_ops_WorkOrder PRIMARY KEY,    customer_code varchar(16) NOT NULL,    region_code char(3) NOT NULL,    status varchar(16) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    closed_at datetime2(0) NULL,    amount decimal(12,2) NOT NULL,    description nvarchar(400) NULL,    CONSTRAINT CK_ops_WorkOrder_status      CHECK (status IN ('OPEN','ASSIGNED','CLOSED','ESCALATED')),    CONSTRAINT CK_ops_WorkOrder_priority CHECK (priority BETWEEN 1 AND 5)  );  ;WITH n AS  (    SELECT TOP (30000)      ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT ops.WorkOrder(customer_code,region_code,status,priority,opened_at,closed_at,amount,description)  SELECT CONCAT('CUST-',RIGHT('000000'+CONVERT(varchar(6),n%9000),6)),         CASE n%3 WHEN 0 THEN 'N01' WHEN 1 THEN 'W02' ELSE 'E03' END,         CASE WHEN n%23=0 THEN 'ESCALATED'              WHEN n%5=0 THEN 'CLOSED'              WHEN n%2=0 THEN 'ASSIGNED' ELSE 'OPEN' END,         CONVERT(tinyint,1+n%5),         DATEADD(minute,n,'2026-01-01T00:00:00'),         CASE WHEN n%5=0 THEN DATEADD(minute,n+60,'2026-01-01T00:00:00') END,         CAST(25+(n%25000)/10.0 AS decimal(12,2)),         CONCAT(N'ServiceHub work order ',n)  FROM n;  CREATE INDEX IX_ops_WorkOrder_region_status_opened    ON ops.WorkOrder(region_code,status,opened_at)    INCLUDE(customer_code,priority,amount,closed_at);END;GO

USE ServiceHubProgrammabilityLab;GOCREATE OR ALTER VIEW api.vOpenWorkOrderASSELECT work_order_id,customer_code,region_code,priority,opened_at,amountFROM ops.WorkOrderWHERE status='OPEN';GOSELECT TOP (5) *FROM api.vOpenWorkOrderORDER BY opened_at DESC,work_order_id DESC;GOSELECT OBJECT_DEFINITION(OBJECT_ID(N'api.vOpenWorkOrder')) AS stored_definition;GO

The view stores the SELECT definition and exposes it as a table-like object. SQL Server normally expands/optimizes the view definition with the caller’s query. There is no independent cache of result rows just because a view exists. Plan caching is separate, and an indexed view is a different object because its unique clustered index stores the materialized result.

Views are useful for naming stable projections, hiding base-table complexity, centralizing some relational rules, and applying permissions at a module boundary. They also introduce coupling: callers may depend on column names/types/order, while nested layers of views can make lineage and tuning harder to see.

2. Updatability depends on whether SQL Server can map the change unambiguously

A simple single-table view can often be updated, and the modification flows to the base table. Aggregation, many joins, DISTINCT and other constructs can make a view non-updatable because there is no unambiguous base-row operation. An INSTEAD OF trigger can implement custom behavior, but then you have explicitly written that translation logic and must test multi-row semantics.

BEGIN TRAN;DECLARE @id bigint=(SELECT MIN(work_order_id) FROM api.vOpenWorkOrder);SELECT work_order_id,priority FROM ops.WorkOrder WHERE work_order_id=@id;UPDATE api.vOpenWorkOrder SET priority=5 WHERE work_order_id=@id;SELECT work_order_id,priority FROM ops.WorkOrder WHERE work_order_id=@id;ROLLBACK;GO

This transaction demonstrates mapping, not a recommendation to use views as write APIs. If an application’s write contract has validation, authorization and transaction semantics, a procedure is often easier to version and test.

3. SCHEMABINDING creates a deployment boundary

A schema-bound view must use two-part object names and prevents incompatible changes to referenced objects while the dependency exists. This can protect the contract, but it also means deployments must coordinate view changes with table changes instead of relying on accidental breakage.

SET ANSI_NULLS ON;SET QUOTED_IDENTIFIER ON;GOCREATE OR ALTER VIEW api.vWorkOrderBoundWITH SCHEMABINDINGASSELECT work_order_id,customer_code,region_code,status,priority,opened_at,amountFROM ops.WorkOrder;GOBEGIN TRY  ALTER TABLE ops.WorkOrder DROP COLUMN description;END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS expected_error,ERROR_MESSAGE() AS expected_message;END CATCH;GO

The failed ALTER TABLE is evidence that the dependency boundary is enforced. It does not mean schema binding is always desirable; it means you choose coupling deliberately and update the dependent module during deployments.

4. Indexed views: materialization with strict rules

An indexed view begins as a schema-bound deterministic view. The first index must be a unique clustered index; that index physically materializes the view result. SQL Server must maintain that materialized result whenever referenced base data changes. This can make selected read workloads faster and selected write workloads more expensive.

Microsoft requires a fixed set of session options when indexed views are created, maintained, and considered by the optimizer. The critical values include ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL and QUOTED_IDENTIFIER ON, with NUMERIC_ROUNDABORT OFF. The base tables and view must have the same owner, and grouped indexed views require COUNT_BIG(*).

SET NUMERIC_ROUNDABORT OFF;SET ANSI_PADDING ON;SET ANSI_WARNINGS ON;SET CONCAT_NULL_YIELDS_NULL ON;SET ARITHABORT ON;SET QUOTED_IDENTIFIER ON;SET ANSI_NULLS ON;GOCREATE OR ALTER VIEW api.vRegionStatusSummaryWITH SCHEMABINDINGASSELECT region_code,status,COUNT_BIG(*) AS order_count,       SUM(CONVERT(bigint,priority)) AS priority_totalFROM ops.WorkOrderGROUP BY region_code,status;GOIF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID(N'api.vRegionStatusSummary') AND name=N'CUX_vRegionStatusSummary')  CREATE UNIQUE CLUSTERED INDEX CUX_vRegionStatusSummary    ON api.vRegionStatusSummary(region_code,status);GOSELECT * FROM api.vRegionStatusSummary WITH (NOEXPAND)ORDER BY region_code,status;GO

NOEXPAND tells SQL Server to treat the indexed view like a table and use its indexed representation when the view is explicitly referenced. Automatic matching—where the optimizer substitutes an indexed view even when the query names base tables—has edition/support considerations, so verify the target edition and actual plan rather than promising substitution.

5. Observe the write cost instead of calling materialization free

SELECT i.name,i.type_desc,i.is_unique,p.rowsFROM sys.indexes AS iJOIN sys.partitions AS p ON p.object_id=i.object_id AND p.index_id=i.index_idWHERE i.object_id=OBJECT_ID(N'api.vRegionStatusSummary');GOSELECT index_id,leaf_insert_count,leaf_update_count,leaf_delete_count,       leaf_page_merge_countFROM sys.dm_db_index_operational_stats (DB_ID(),OBJECT_ID(N'api.vRegionStatusSummary'),NULL,NULL);GO

Every qualifying INSERT/UPDATE/DELETE against ops.WorkOrder may also require maintenance of the indexed view. On a write-heavy OLTP workload, that can create CPU, logging, locking and storage costs that outweigh the reporting benefit. Measure representative DML with the indexed view present and removed; do not infer write amplification from the index name alone.

6. Cleanup and rollback

DROP INDEX IF EXISTS CUX_vRegionStatusSummary ON api.vRegionStatusSummary;DROP VIEW IF EXISTS api.vRegionStatusSummary;DROP VIEW IF EXISTS api.vWorkOrderBound;GO

Dropping the unique clustered index removes the materialized indexed representation; dropping the view removes the definition. In production, removal should still follow dependency review and Query Store regression monitoring because callers or plans may depend on the object.

Production judgment

Use ordinary views to publish relational abstractions with stable contracts. Keep the layering shallow enough that operators can trace a slow query back to base tables and predicates. Use schema binding when deployment protection is worth the coupling. Use indexed views only after workload evidence proves a materialized aggregate/projection is valuable enough to pay ongoing maintenance cost and after all required SET/ownership/determinism constraints are operationally enforceable.

The ordinary-view lab works on free Developer/Express. The indexed-view mandatory experiment is designed for free SQL Server 2025 Developer; exact automatic indexed-view matching behavior is edition-sensitive. Compatibility 170 is used for course consistency, but indexed-view fundamentals predate it.

Authoritative references

• Create indexed views — SET options, ownership, schema binding, unique clustered index and DML costs
• CREATE VIEW — view definition and updatability
• Table hints — NOEXPAND — indexed-view matching and NOEXPAND considerations
• CREATE INDEX — index-on-view prerequisites
• SQL Server 2025 build versions — servicing baseline