Create and use views

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Create and query a reusable projection inside a rolled-back teaching transaction. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

BEGIN TRANSACTION;
EXEC(N'CREATE VIEW dbo.DDI_LessonCustomerView AS
    SELECT CustomerID, FirstName, City FROM dbo.Customers');
SELECT CustomerID, FirstName, City
FROM dbo.DDI_LessonCustomerView WHERE CustomerID BETWEEN 1 AND 3
ORDER BY CustomerID;
ROLLBACK TRANSACTION;

Read it step by step

1. CREATE VIEW defines a named SELECT; EXEC makes the CREATE VIEW the first statement in its dynamic batch.
2. The following SELECT queries the view like a table.
3. ROLLBACK removes the practice view so the canonical database object set stays stable.

Check the result

The view stores the query definition, not a separate copy of these customer rows.

Practice

Create a disposable view of ProductID and ProductName inside a transaction, read Products 1–2, then roll back.

BEGIN TRANSACTION;
EXEC(N'CREATE VIEW dbo.DDI_LessonProductView AS
    SELECT ProductID, ProductName FROM dbo.Products');
SELECT ProductID, ProductName FROM dbo.DDI_LessonProductView
WHERE ProductID BETWEEN 1 AND 2 ORDER BY ProductID;
ROLLBACK TRANSACTION;