Views in T-SQL

A view is a saved query with a name. Query it like a table, and the long join inside is written once. Create it with CREATE OR ALTER VIEW, in a batch of its own.

Journeyman · T-SQL · SQL · Updated

By

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;
ProductName CategoryName SupplierName UnitPrice
Chai Beverages Exotic Liquids 18.00
Chang Beverages Exotic Liquids 19.00
Aniseed Syrup Condiments Exotic Liquids 10.00
Chef Anton's Cajun Seasoning Condiments New Orleans Cajun Delights 22.00

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;
ProductName SupplierName UnitPrice
Manjimup Dried Apples G'day, Mate 53.00
Rössle Sauerkraut Plutzer Lebensmittelgroßmärkte AG 45.60
Uncle Bob's Organic Dried Pears Grandma Kelly's Homestead 30.00
Tofu Mayumi's 23.25
Longlife Tofu Tokyo Traders 10.00

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;.

Do it: save the orders-and-customers join as a view

Every order report needs the same join: each order beside the name of the customer who placed it. Save that join once, as a view, so every report can read it like a table.

Two tables hold what the view needs:

  • Customers: each customer's CustomerId and CompanyName.
  • Orders: each order's OrderId, the CustomerId of the customer who placed it, and its OrderDate.

They hold these rows (the Tables tab shows the SQL that makes them):

CustomerId CompanyName
ALFKI Alfreds Futterkiste
ANATR Ana Trujillo Emparedados y helados
AROUT Around the Horn
OrderId CustomerId OrderDate
10308 ANATR 2016-09-18
10355 AROUT 2016-11-15
10643 ALFKI 2017-08-25
10692 ALFKI 2017-10-03

For example, order 10308's CustomerId is ANATR, and ANATR is Ana Trujillo Emparedados y helados, so the view's row for it is 10308 | Ana Trujillo Emparedados y helados | 2016-09-18.

Once your view exists, the box runs SELECT * FROM OrderList;, and it should give exactly these rows, in any order:

OrderId Customer OrderDate
10308 Ana Trujillo Emparedados y helados 2016-09-18
10355 Around the Horn 2016-11-15
10643 Alfreds Futterkiste 2017-08-25
10692 Alfreds Futterkiste 2017-10-03

Your job: create a view named OrderList.

  • Start with CREATE VIEW OrderList AS. (The box's database doesn't know CREATE OR ALTER; CREATE VIEW works in every database.)
  • Inside it, join Orders to Customers on CustomerId.
  • Give the view three columns, in this order: OrderId, Customer (the customer's CompanyName, renamed) and OrderDate.
  • Leave out ORDER BY: a view can't hold one, so a report sorts when it reads the view.

Practiced live on the lesson page: write the SQL right there and check it.

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

Cheat sheets