LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 2918 min read

Views

Learn how to create and query SQL Server views, and why they are so useful for simplifying complex or frequently repeated queries.

Introduction

As your database grows, you will often find yourself writing the same complex query - joining several tables and filtering rows in a very specific way - over and over again in reports, applications, and ad-hoc queries. A view lets you save that query under a name and reuse it as if it were a regular table. In this lesson, you will learn what a view is, how to create one, and why views are one of the most practical tools for keeping T-SQL code clean and consistent.

What You Will Learn
  • What a view is and how it differs from a table.
  • How to create a view with CREATE VIEW.
  • How to query a view exactly like a table.
  • When a view can be updated, and when it cannot.
  • How to alter and drop views safely.

Why Use Views?

A view is a virtual table backed by a stored SELECT statement. It does not store data itself (unless you create an indexed view, a more advanced topic); instead, every time you query the view, SQL Server runs the underlying query and returns the results. Views are useful for several reasons: they hide complexity behind a simple name, they let you expose only certain columns or rows to certain users, and they keep business logic consistent because everyone queries the same definition instead of copying and pasting SQL.

SituationWhy a View Helps
A report joins 5 tables every timeWrap the join once in a view; reports just SELECT FROM the view
Users should only see active customersThe view can filter WHERE IsActive = 1, hiding inactive rows
A table has sensitive columnsThe view can expose only the safe columns, hiding others
Multiple apps need the same calculated columnDefine the calculation once in the view instead of duplicating it

Creating a View

You create a view with CREATE VIEW followed by a name and an AS clause containing a SELECT statement. The example below assumes a Customers table with an IsActive flag.

CREATE VIEW dbo.vwActiveCustomers AS
SELECT
    CustomerID,
    FirstName,
    LastName,
    Email,
    City
FROM dbo.Customers
WHERE IsActive = 1;
GO
GO Separates Batches

CREATE VIEW must be the first statement in its batch. If you are running this in the same script as other statements, put GO on the line before CREATE VIEW to start a new batch.

Querying a View

Once created, you query a view exactly like a table - with SELECT, WHERE, ORDER BY, and even joins to other tables.

SELECT CustomerID, FirstName, LastName, City
FROM dbo.vwActiveCustomers
WHERE City = 'Seattle'
ORDER BY LastName;
Result

Click Run to see what this code prints.

Views Over Joins

Views are especially valuable when the underlying query involves multiple joins. Instead of every report re-writing the join logic, they simply select from the view.

CREATE VIEW dbo.vwOrderSummary AS
SELECT
    o.OrderID,
    c.FirstName + ' ' + c.LastName AS CustomerName,
    o.OrderDate,
    SUM(od.Quantity * od.UnitPrice) AS OrderTotal
FROM dbo.Orders o
JOIN dbo.Customers c   ON c.CustomerID = o.CustomerID
JOIN dbo.OrderDetails od ON od.OrderID = o.OrderID
GROUP BY o.OrderID, c.FirstName, c.LastName, o.OrderDate;
GO


SELECT TOP 3 * FROM dbo.vwOrderSummary ORDER BY OrderTotal DESC;
Result

Click Run to see what this code prints.

Updatable Views

A view built on a single table with no aggregates, DISTINCT, GROUP BY, or set operators is generally updatable - you can run INSERT, UPDATE, or DELETE against it and SQL Server applies the change to the base table. dbo.vwActiveCustomers is a good example, since it comes from a single table with simple columns.

UPDATE dbo.vwActiveCustomers
SET City = 'Redmond'
WHERE CustomerID = 104;
Multi-Table Views Are Usually Not Updatable

dbo.vwOrderSummary joins three tables and uses GROUP BY, so SQL Server will reject direct INSERT/UPDATE/DELETE statements against it. For views like that, treat them as read-only reporting views.

Altering and Dropping Views

Use ALTER VIEW to change a view's definition without dropping and recreating any permissions granted on it, and DROP VIEW to remove it entirely.

ALTER VIEW dbo.vwActiveCustomers AS
SELECT CustomerID, FirstName, LastName, Email, City, Country
FROM dbo.Customers
WHERE IsActive = 1;
GO


DROP VIEW IF EXISTS dbo.vwOldReport;

Common Mistakes

Avoid These Mistakes
  • Using SELECT * inside a view definition - if columns are later added to the base table, the view's behavior can silently change.
  • Nesting views many layers deep, which makes the actual query plan hard to reason about and can hurt performance.
  • Assuming every view is updatable - views with joins, GROUP BY, or DISTINCT are not.
  • Forgetting that a view does not store data (unless indexed), so querying it still costs the same as the underlying query.
  • Not granting SELECT permission on the view itself when trying to hide the base tables from a user.

Best Practices

  • List columns explicitly in a view instead of using SELECT *.
  • Use a consistent naming prefix like vw for views so they are easy to spot in object lists.
  • Use views to enforce row-level and column-level security instead of granting direct table access.
  • Keep views focused on one purpose - a reporting view and a security view can be separate objects.
  • Document non-obvious business logic (like which rows count as 'active') directly in the view so it is defined in one place.

Frequently Asked Questions

Not by itself. SQL Server expands the view's definition into the outer query at execution time, so performance depends on the underlying query and indexes, not on the fact that a view was used.

Yes, but keep nesting shallow. Deeply nested views make it harder to predict the final execution plan and to debug unexpected results.

An indexed (or 'materialized') view has a unique clustered index built on it, which physically stores the result set and can speed up expensive aggregations. It is a more advanced, more restrictive feature covered separately from ordinary views.

No - views cannot take parameters. If you need parameterized, reusable logic, use an inline table-valued function instead, which behaves like a parameterized view.

Key Takeaways

  • A view is a saved SELECT statement that you can query like a table.
  • Views simplify complex or repeated queries and can hide columns or rows for security.
  • Single-table views without aggregates are usually updatable; multi-table views generally are not.
  • Views do not store data on their own, so their performance depends on the underlying query.
  • Use ALTER VIEW to change a definition and DROP VIEW to remove it.

Summary

Views let you package a query once and reuse it everywhere, which keeps reporting logic consistent and can simplify what end users and applications need to know about your schema. Next, you will look at indexes - one of the most important tools for making those queries (view-based or not) run fast.

Next Lesson →

Indexes