Views in T-SQL

After this lesson you can

• Create a view with CREATE OR ALTER VIEW, and query it like a table
• Explain what views are good for, and what they can't do

Key takeaways

• A view is a saved query with a name. Create it with CREATE OR ALTER VIEW, then query it like a table.
• A CREATE VIEW must be the only statement in its batch, so put GO before and after it in a longer script.
• Every column in a view needs a name, so give a calculated or duplicate column an alias.
• A view stores no data: SQL Server runs its query each time you use it, so the results are always up to date.
• A view can't take parameters or hold an ORDER BY (except with TOP), so filter and sort when you query it.

Some queries get written again and again: every report about products needs the same three-table join to show category and supplier names. A view saves a query in the database, under a name. From then on, you query the view as if it were a table.

Creating a view

CREATE OR ALTER VIEW ProductDetails
AS
SELECT p.ProductId
    , p.ProductName
    , c.CategoryName
    , s.CompanyName AS SupplierName
    , p.UnitPrice
    , p.UnitsInStock
    , p.Discontinued
FROM Products AS p
INNER JOIN Categories AS c
    ON p.CategoryId = c.CategoryId
INNER JOIN Suppliers AS s
    ON p.SupplierId = s.SupplierId;

• CREATE OR ALTER VIEW makes the view, or replaces it if it already exists, so you can run the script again after changing it. (CREATE VIEW alone fails if the view exists; DROP VIEW ProductDetails removes one.)
• After AS comes an ordinary SELECT.
• Every column needs a name, so a calculated or duplicate column needs an alias. (SupplierName isn't required here, but it says more than CompanyName would.)
• A CREATE VIEW must be the only statement in its batch. In a script with other statements, put GO on a line of its own before and after it.

In SSMS, the new view appears under Views in Object Explorer, after a refresh (F5).

Querying a view

A view behaves like a table in a SELECT:

SELECT TOP (4) ProductName, CategoryName, SupplierName, UnitPrice
FROM ProductDetails
ORDER BY ProductId;

Filter it, sort it, group it, join it to other tables: everything works.

SELECT ProductName, SupplierName, UnitPrice
FROM ProductDetails
WHERE CategoryName = 'Produce'
ORDER BY UnitPrice DESC;

A view stores no data. Each time you query it, SQL Server runs the query inside it, so the results are always up to date.

What views are good for

• Write a join once. Every report uses the view, and if the join ever changes, it changes in one place.
• Hide what doesn't matter. A view can leave out columns a report doesn't need, or that some users shouldn't see.
• Give a simple name to a complex question. ProductDetails says what it holds; the query inside doesn't have to.

What views can't do

• No parameters. A view always runs the same query; filter it with WHERE when you use it. (For parameters, use a stored procedure or a function, the next lessons.)
• No ORDER BY inside, unless with TOP. Sort when you query the view.

The course's views

The course's sample databases come with views scripts: Northwind_Views.sql creates CustomerOrders, which joins customers, orders, order lines, products and categories; AdventureWorksLT_Views.sql creates three views in AdventureWorksLT. Open each in SSMS, choose its database in the drop-down (Northwind for Northwind_Views.sql, AdventureWorksLT2022 for AdventureWorksLT_Views.sql), and press F5. Then, with Northwind chosen, try SELECT TOP (10) * FROM CustomerOrders;.

Check yourself

2 questions to try on the lesson page.

So what

A view turns a query you'd otherwise copy everywhere into something with a name. Your C# code can then read from ProductDetails as simply as from Products, and the join lives in one place.

Key terms

• View
• CREATE VIEW
• CREATE OR ALTER
• GO Batch Separator

Cheat sheets

• Views in T-SQL