Views

Requirements: SCHEMABINDING, two-part names, deterministic expressions, COUNT_BIG(*) when grouping, specific SET options, no outer joins, and no subqueries.

16.1 Creating Views

CREATE OR ALTER VIEW Sales.vw_CustomerOrders
AS
SELECT c.CustomerID, c.FirstName, c.LastName, o.OrderID, o.OrderDate, o.Status
FROM dbo.Customers c JOIN dbo.Orders o ON o.CustomerID = c.CustomerID;
GO
SELECT * FROM Sales.vw_CustomerOrders WHERE Status = 'Pending';

16.2 View Options

WITH SCHEMABINDING: prevents changes to the underlying tables. Required for indexed views.

WITH ENCRYPTION: obfuscates the definition (not real security).

WITH CHECK OPTION: modifications through the view must satisfy the view's WHERE clause.

WITH VIEW_METADATA

CREATE VIEW dbo.vw_IndiaCustomers
AS SELECT CustomerID, FirstName, Country FROM dbo.Customers WHERE Country = N'India'
WITH CHECK OPTION;

16.3 Updatable Views

An INSERT or UPDATE can modify only one base table at a time.

The affected columns can't be derived through aggregates, DISTINCT, GROUP BY, or computations.

Use INSTEAD OF triggers for complex views.

16.4 Indexed (Materialized) Views

Requirements: SCHEMABINDING, two-part names, deterministic expressions, COUNT_BIG(*) when grouping, specific SET options, no outer joins, and no subqueries.

CREATE VIEW dbo.vw_SalesByProduct WITH SCHEMABINDING
AS
SELECT ProductID, SUM(LineTotal) AS Total, COUNT_BIG(*) AS Cnt
FROM dbo.OrderItems GROUP BY ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX IX_vw_SalesByProduct ON dbo.vw_SalesByProduct(ProductID);
-- Query with NOEXPAND hint in non-Enterprise editions
SELECT * FROM dbo.vw_SalesByProduct WITH (NOEXPAND);

16.5 Other View Types

Partitioned views: UNION ALL across member tables with CHECK constraints.

Distributed partitioned views: member tables span linked servers.

System views: catalog views (sys.*), INFORMATION_SCHEMA, and DMVs (sys.dm_*).