Views Reference — SQL Server 2022

-- Minimal form
CREATE VIEW Sales.OrderSummary
AS
    SELECT
        o.OrderID,
        o.CustomerID,
        c.CustomerName,
        o.OrderDate,
        SUM(od.Qty * od.UnitPrice) AS TotalAmount
    FROM Sales.Orders         AS o
    JOIN Sales.OrderDetails   AS od ON od.OrderID = o.OrderID
    JOIN Sales.Customers      AS c  ON c.CustomerID = o.CustomerID
    GROUP BY o.OrderID, o.CustomerID, c.CustomerName, o.OrderDate;
GO

-- ALTER to modify
ALTER VIEW Sales.OrderSummary
AS
    -- revised query here
GO

-- Drop
DROP VIEW IF EXISTS Sales.OrderSummary;  -- IF EXISTS: SQL Server 2016+

CREATE VIEW dbo.ProductMargin
AS
    SELECT
        ProductID,
        ProductName,
        ListPrice - StandardCost  AS Margin,   -- explicit alias required
        (ListPrice - StandardCost) / NULLIF(ListPrice, 0) AS MarginPct
    FROM Production.Product;

CREATE VIEW dbo.SensitiveCalc
WITH ENCRYPTION
AS
    SELECT ...;

CREATE VIEW Sales.ActiveCustomers
AS
    SELECT CustomerID, CustomerName, IsActive
    FROM Sales.Customers
    WHERE IsActive = 1
WITH CHECK OPTION;

-- This INSERT would be blocked — the new row has IsActive = 0, which the view wouldn't show:
INSERT INTO Sales.ActiveCustomers (CustomerID, CustomerName, IsActive)
VALUES (999, 'Ghost Corp', 0);
-- Msg 550, The attempted insert or update failed because the target view either specifies WITH CHECK OPTION...

-- Updateable — passes all conditions:
CREATE VIEW HR.ActiveEmployees
AS
    SELECT EmployeeID, FirstName, LastName, DepartmentID
    FROM HR.Employees
    WHERE IsActive = 1;

-- Valid UPDATE through the view:
UPDATE HR.ActiveEmployees
SET DepartmentID = 5
WHERE EmployeeID = 101;

-- Valid DELETE through the view:
DELETE FROM HR.ActiveEmployees WHERE EmployeeID = 101;

CREATE VIEW dbo.ProductCatalog
WITH SCHEMABINDING
AS
    SELECT
        p.ProductID,
        p.ProductName,
        c.CategoryName
    FROM dbo.Products     AS p   -- must use two-part names: schema.object
    JOIN dbo.Categories   AS c ON c.CategoryID = p.CategoryID;

-- Without SCHEMABINDING: this succeeds silently, breaking the view
ALTER TABLE dbo.Products DROP COLUMN ProductName;  -- view now returns error at query time

-- With SCHEMABINDING: this fails fast with a clear error
ALTER TABLE dbo.Products DROP COLUMN ProductName;
-- Msg 5074, The object 'ProductCatalog' is dependent on column 'ProductName'.

-- Step 1: Create the view with SCHEMABINDING
CREATE VIEW Sales.DailySalesByProduct
WITH SCHEMABINDING
AS
    SELECT
        CAST(o.OrderDate AS date)   AS SaleDate,
        od.ProductID,
        SUM(od.Qty * od.UnitPrice)  AS TotalSales,
        COUNT_BIG(*)                AS OrderLineCount   -- required with GROUP BY
    FROM dbo.Orders         AS o
    JOIN dbo.OrderDetails   AS od ON od.OrderID = o.OrderID
    GROUP BY CAST(o.OrderDate AS date), od.ProductID;
GO

-- Step 2: Create unique clustered index — this materializes the view
CREATE UNIQUE CLUSTERED INDEX CIX_DailySalesByProduct
ON Sales.DailySalesByProduct (SaleDate, ProductID);
GO

-- Step 3: Add nonclustered indexes for additional access patterns
CREATE NONCLUSTERED INDEX IX_DailySalesByProduct_Product
ON Sales.DailySalesByProduct (ProductID)
INCLUDE (TotalSales);

-- This query references the base tables...
SELECT
    CAST(OrderDate AS date) AS SaleDate,
    ProductID,
    SUM(Qty * UnitPrice)    AS TotalSales
FROM dbo.Orders AS o
JOIN dbo.OrderDetails AS od ON od.OrderID = o.OrderID
GROUP BY CAST(OrderDate AS date), ProductID;

-- ...and the optimizer may choose the indexed view instead if it's cheaper.
-- Look for "Sales.DailySalesByProduct" in the execution plan.

-- Explicit reference to the view + NOEXPAND forces materialized scan/seek
SELECT SaleDate, ProductID, TotalSales
FROM Sales.DailySalesByProduct WITH (NOEXPAND)
WHERE SaleDate >= '2024-01-01';

ALTER INDEX CIX_DailySalesByProduct
ON Sales.DailySalesByProduct
REBUILD WITH (ONLINE = ON);   -- ONLINE requires Enterprise Edition

SELECT
    OBJECT_NAME(i.object_id)  AS ViewName,
    i.name                    AS IndexName,
    s.avg_fragmentation_in_percent,
    s.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Sales.DailySalesByProduct'), NULL, NULL, 'LIMITED') AS s
JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.index_id;

-- Member tables: one per year, each with a CHECK constraint
CREATE TABLE Orders_2022 (
    OrderID   int           NOT NULL,
    OrderYear smallint      NOT NULL CONSTRAINT CK_Orders2022_Year CHECK (OrderYear = 2022),
    -- other columns
    CONSTRAINT PK_Orders2022 PRIMARY KEY (OrderID, OrderYear)
);

CREATE TABLE Orders_2023 (
    OrderID   int           NOT NULL,
    OrderYear smallint      NOT NULL CONSTRAINT CK_Orders2023_Year CHECK (OrderYear = 2023),
    CONSTRAINT PK_Orders2023 PRIMARY KEY (OrderID, OrderYear)
);

CREATE TABLE Orders_2024 (
    OrderID   int           NOT NULL,
    OrderYear smallint      NOT NULL CONSTRAINT CK_Orders2024_Year CHECK (OrderYear = 2024),
    CONSTRAINT PK_Orders2024 PRIMARY KEY (OrderID, OrderYear)
);

-- Partitioned view
CREATE VIEW dbo.Orders
AS
    SELECT * FROM dbo.Orders_2022
    UNION ALL
    SELECT * FROM dbo.Orders_2023
    UNION ALL
    SELECT * FROM dbo.Orders_2024;

-- Only Orders_2023 is accessed — optimizer uses CHECK constraints to eliminate others
SELECT OrderID, OrderYear
FROM dbo.Orders
WHERE OrderYear = 2023;

-- On Server1 (owns 2022–2023 data):
CREATE VIEW dbo.Orders
AS
    SELECT * FROM dbo.Orders_2022
    UNION ALL
    SELECT * FROM dbo.Orders_2023
    UNION ALL
    SELECT * FROM Server2.SalesDB.dbo.Orders_2024;   -- remote member

-- Constraint is untrusted (is_not_trusted = 1) — re-check it:
ALTER TABLE dbo.Orders_2022
WITH CHECK CHECK CONSTRAINT CK_Orders2022_Year;

-- List all views in the current database
SELECT
    SCHEMA_NAME(schema_id)  AS SchemaName,
    name                    AS ViewName,
    is_schema_bound,        -- 1 = WITH SCHEMABINDING
    with_check_option,
    create_date,
    modify_date
FROM sys.views
ORDER BY SchemaName, ViewName;

-- Get view definition (not encrypted views)
SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.OrderSummary'));
-- or:
EXEC sp_helptext 'Sales.OrderSummary';

-- Find all indexed views (have a clustered index)
SELECT
    v.name AS ViewName,
    i.name AS IndexName,
    i.type_desc
FROM sys.views AS v
JOIN sys.indexes AS i ON i.object_id = v.object_id AND i.type = 1  -- clustered
ORDER BY v.name;

-- Check dependencies — what objects does this view reference?
SELECT
    OBJECT_NAME(referencing_id)  AS ViewName,
    referenced_schema_name,
    referenced_entity_name,
    referenced_minor_name        -- column name, if column-level dep
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('Sales.OrderSummary');

-- Reverse: what views reference this table?
SELECT
    OBJECT_NAME(referencing_id) AS DependentView
FROM sys.sql_expression_dependencies
WHERE referenced_id = OBJECT_ID('Sales.Orders');

-- If a user has SELECT on the view, they can still see all columns returned by the view.
-- Use column-level DENY or RLS predicates for true column/row security.
-- See references/16-security-encryption.md for RLS.

CREATE VIEW dbo.EmployeeFull AS SELECT * FROM dbo.Employees;

ALTER TABLE dbo.Employees ADD Salary money;

-- The view still returns the OLD column list until refreshed:
EXEC sp_refreshview 'dbo.EmployeeFull';
-- Or: run ALTER VIEW to recompile

-- ViewC references ViewB which references ViewA which has a 5-table join.
-- All joins execute on every query of ViewC. There is no caching.
-- Flatten nested views into a single view or indexed view when nesting causes plan complexity.

SET STATISTICS IO ON;
-- Run your INSERT workload
-- Look for logical reads on the indexed view's clustered index name

-- Wrong — will fail to create clustered index:
CREATE VIEW dbo.BadAgg WITH SCHEMABINDING AS
    SELECT ProductID, SUM(Qty) AS TotalQty
    FROM dbo.OrderDetails GROUP BY ProductID;

-- Right:
CREATE VIEW dbo.GoodAgg WITH SCHEMABINDING AS
    SELECT ProductID, SUM(Qty) AS TotalQty, COUNT_BIG(*) AS RowCount
    FROM dbo.OrderDetails GROUP BY ProductID;

-- GETDATE() is non-deterministic — will fail at index creation time:
CREATE VIEW dbo.BadIndexed WITH SCHEMABINDING AS
    SELECT ProductID, DATEDIFF(day, CreateDate, GETDATE()) AS AgeDays
    FROM dbo.Products;
-- Cannot create index — GETDATE() is non-deterministic