# 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 November 3, 2026

## 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

```sql
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`:

```sql
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.

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

## 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](https://www.ask-codey.com/glossary/view)
- [CREATE VIEW](https://www.ask-codey.com/glossary/create-view)
- CREATE OR ALTER
- GO Batch Separator

## Cheat sheets

- [Views in T-SQL](https://www.ask-codey.com/cheat-sheets/views-in-t-sql-cheat-sheet)

Source: https://www.ask-codey.com/lessons/views-in-t-sql