Lesson 36 of 54 16 minAdvanced

Views

Save a query under a name and use it like a table: creating views, naming their columns, why ORDER BY is not allowed, the SELECT * trap, updating through a view, WITH CHECK OPTION, SCHEMABINDING and views for security.

You have written the same join between Products and Categories many times. A view saves a SELECT under a name. After that you can query the view as if it were a table, and SQL Server runs the saved SELECT for you.

Query.sql

1CREATE OR ALTER VIEW dbo.ProductList2AS3SELECT p.ProductId, p.Name, c.Name AS Category, p.Price, p.Stock4FROM dbo.Products AS p5LEFT JOIN dbo.Categories AS c ON c.CategoryId = p.CategoryId;6GO7SELECT Name, Category, Price8FROM dbo.ProductList9WHERE Category = N'Tools'10ORDER BY Price DESC;

Output

Name              Category  Price
----------------  --------  -----
Cordless drill    Tools     89.00
Claw hammer       Tools     18.50
Tape measure 8 m  Tools      9.75

CREATE OR ALTER creates the view, or replaces it if it already exists, so you can run the script again. The GO line ends the batch: a CREATE VIEW must be in a batch of its own, which the next lesson explains.

A view stores no data, only its SELECT. Every query against it runs that SELECT again, so a view always shows the current rows. SQL Server merges the view’s SELECT into your query before it plans it, so a view is usually no slower than writing the join yourself.

Every column needs a name

A view is used like a table, so each of its columns needs a name. An expression without an alias stops the view being created:

Query.sql

1CREATE OR ALTER VIEW dbo.OrderTotals2AS3SELECT o.OrderId, o.CustomerId, o.OrderDate, o.Status,4       SUM(i.Quantity * i.UnitPrice)5FROM dbo.Orders AS o6JOIN dbo.OrderItems AS i ON i.OrderId = o.OrderId7GROUP BY o.OrderId, o.CustomerId, o.OrderDate, o.Status;

Output

Msg 4511, Level 16, State 1, Procedure OrderTotals, Line 3
Create View or Function failed because no column name was specified for column 5.

Name the expressions and the view works. Views can then be joined and grouped like any table. Here one view answers a question that would otherwise need a join inside a join:

Query.sql

1CREATE OR ALTER VIEW dbo.OrderTotals2AS3SELECT o.OrderId, o.CustomerId, o.OrderDate, o.Status,4       COUNT(*) AS Lines,5       SUM(i.Quantity * i.UnitPrice) AS Total6FROM dbo.Orders AS o7JOIN dbo.OrderItems AS i ON i.OrderId = o.OrderId8GROUP BY o.OrderId, o.CustomerId, o.OrderDate, o.Status;9GO10SELECT c.FirstName, COUNT(*) AS Orders, SUM(t.Total) AS Spent11FROM dbo.OrderTotals AS t12JOIN dbo.Customers AS c ON c.CustomerId = t.CustomerId13WHERE t.Status <> 'Cancelled'14GROUP BY c.CustomerId, c.FirstName15ORDER BY Spent DESC;

Output

FirstName  Orders   Spent
---------  ------  ------
Dev             2  723.50
Farid           2  211.85
Asha            2  150.80
Ben             2  131.00
Zoë             1  102.50

No ORDER BY inside a view

A view is a set of rows, like a table, and a set has no order. SQL Server refuses an ORDER BY in a view:

Query.sql

1CREATE OR ALTER VIEW dbo.DearProducts2AS3SELECT Name, Price4FROM dbo.Products5WHERE Price > 206ORDER BY Price DESC;

Output

Msg 1033, Level 15, State 1, Procedure DearProducts, Line 6
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Sort in the query that reads the view: SELECT * FROM dbo.DearProducts ORDER BY Price DESC.

The SELECT * trap

When a view is created, SQL Server records its column list. SELECT * is expanded once, at that moment. Columns added to the table later do not appear in the view:

Query.sql

1DROP TABLE IF EXISTS dbo.Suppliers;2CREATE TABLE dbo.Suppliers (3    SupplierId int          NOT NULL PRIMARY KEY,4    Name       nvarchar(60) NOT NULL5);6INSERT INTO dbo.Suppliers VALUES (1, N'Northern Timber'), (2, N'Fixings Direct');7GO8CREATE OR ALTER VIEW dbo.SupplierList AS SELECT * FROM dbo.Suppliers;9GO10ALTER TABLE dbo.Suppliers ADD Phone varchar(20) NULL;11GO12SELECT * FROM dbo.SupplierList;        -- no Phone column13 14EXEC sp_refreshview N'dbo.SupplierList';15SELECT * FROM dbo.SupplierList;        -- now it has one

Output

SupplierId  Name
----------  ---------------
         1  Northern Timber
         2  Fixings Direct

SupplierId  Name             Phone
----------  ---------------  -----
         1  Northern Timber  NULL
         2  Fixings Direct   NULL

sp_refreshview makes SQL Server read the table’s columns again. It is better not to need it: list the columns in a view, so that the view only changes when you change it.

Changing data through a view

You can INSERT, UPDATE and DELETE through a view when SQL Server can trace every changed column back to one base table. The view’s WHERE still applies, so an UPDATE through a view only reaches the rows the view shows:

Query.sql

1CREATE OR ALTER VIEW dbo.CheapProducts2AS3SELECT ProductId, Name, Price, Stock4FROM dbo.Products5WHERE Price < 10.00;6GO7UPDATE dbo.CheapProducts SET Stock = Stock + 100 WHERE ProductId = 9;  -- Wall plugs: in the view8UPDATE dbo.CheapProducts SET Stock = Stock + 100 WHERE ProductId = 2;  -- Cordless drill: not in it9 10SELECT ProductId, Name, Price, Stock11FROM dbo.Products12WHERE ProductId IN (2, 9);

Output

ProductId  Name              Price  Stock
---------  ----------------  -----  -----
        2  Cordless drill    89.00     12
        9  Wall plugs (100)   4.50    100

The second UPDATE changed nothing: the drill costs 89.00, so it is not in the view. Now raise the price of something that is in the view, past the view’s own limit:

Query.sql

1UPDATE dbo.CheapProducts SET Price = 10.50 WHERE ProductId = 3;  -- Tape measure, was 9.752 3SELECT ProductId, Name, Price4FROM dbo.CheapProducts5ORDER BY Price DESC;

Output

ProductId  Name              Price
---------  ----------------  -----
        4  Cement 25 kg       7.20
        6  Timber 2x4 2.4 m   6.80
       12  Safety goggles     6.80
        9  Wall plugs (100)   4.50
        5  Sand 25 kg         4.10

The update worked, and the tape measure disappeared from the view that changed it. Sometimes that is what you want. When it is not, add WITH CHECK OPTION: SQL Server then rejects any change through the view that would move a row out of it.

Query.sql

1CREATE OR ALTER VIEW dbo.CheapProducts2AS3SELECT ProductId, Name, Price, Stock4FROM dbo.Products5WHERE Price < 10.006WITH CHECK OPTION;7GO8UPDATE dbo.CheapProducts SET Price = 10.50 WHERE ProductId = 4;  -- Cement: would leave the view9UPDATE dbo.CheapProducts SET Price = 7.50  WHERE ProductId = 4;  -- still under 10.00: allowed10 11SELECT ProductId, Name, Price FROM dbo.CheapProducts WHERE ProductId = 4;

Output

Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

ProductId  Name          Price
---------  ------------  -----
        4  Cement 25 kg   7.50

Interactive

Updating through a view

Product

New price WITH CHECK OPTION

CREATE OR ALTER VIEW dbo.CheapProducts AS
SELECT ProductId, Name, Price, Stock
FROM dbo.Products
WHERE Price < 10.00;
GO
UPDATE dbo.CheapProducts
SET Price = 10.50
WHERE ProductId = 4;

(1 row affected)

dbo.CheapProducts after

	ProductId	Name	Price
1	4	Cement 25 kg	10.50
2	3	Tape measure 8 m	9.75
3	6	Timber 2x4 2.4 m	6.80
4	12	Safety goggles	6.80
5	9	Wall plugs (100)	4.50
6	5	Sand 25 kg	4.10

The row in dbo.Products

	ProductId	Name	Price
1	4	Cement 25 kg	10.50

The update worked, and Cement 25 kg has now left the view that changed it (struck through). Tick WITH CHECK OPTION to stop changes like this.

A change that touches columns from two tables cannot go through a view, and neither can a change to a column that is calculated or comes from GROUP BY. SQL Server finds these problems when it compiles the batch, so the whole batch is rejected. That is why each statement below has its own batch:

Query.sql

1UPDATE dbo.ProductList2SET Name = N'Claw hammer 16 oz', Category = N'Hand tools'3WHERE ProductId = 1;4GO5UPDATE dbo.OrderTotals SET Total = 0 WHERE OrderId = 1;6GO7UPDATE dbo.ProductList                 -- one table only: allowed8SET Name = N'Claw hammer 16 oz'9WHERE ProductId = 1;10 11SELECT ProductId, Name, Category FROM dbo.ProductList WHERE ProductId = 1;

Output

Msg 4405, Level 16, State 1, Line 1
View or function 'dbo.ProductList' is not updatable because the modification affects multiple base tables.
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'dbo.OrderTotals' failed because it contains a derived or constant field.

ProductId  Name               Category
---------  -----------------  --------
        1  Claw hammer 16 oz  Tools

SCHEMABINDING: protect what the view depends on

Normally nothing stops someone changing or dropping a table that a view reads, and the view then fails when it is next used. WITH SCHEMABINDING ties the view to the columns it uses, so they cannot be changed while the view exists. It needs two-part names (dbo.Products) and an explicit column list:

Query.sql

1CREATE OR ALTER VIEW dbo.PriceList2WITH SCHEMABINDING3AS4SELECT ProductId, Name, Price5FROM dbo.Products;6GO7ALTER TABLE dbo.Products ALTER COLUMN Name nvarchar(80) NOT NULL;8GO9DROP VIEW dbo.PriceList;   -- so the BuildMart reset script can drop Products again

Output

Msg 5074, Level 16, State 1, Line 1
The object 'PriceList' is dependent on column 'Name'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Name failed because one or more objects access this column.

Schema binding is also required before a view can be indexed: a unique clustered index on a view stores its result, like a table, and SQL Server keeps it up to date on every change. That is a performance tool for the Indexes module.

Views for security

A view can show some columns and rows of a table and hide the rest. Give a team permission on the view, not the table, and they can only ever see what the view shows:

Query.sql

1CREATE OR ALTER VIEW dbo.CustomerDirectory2AS3SELECT CustomerId, FirstName, LastName, City   -- no Email or Phone4FROM dbo.Customers;5GO6SELECT * FROM dbo.CustomerDirectory WHERE City = N'York';

Output

CustomerId  FirstName  LastName  City
----------  ---------  --------  ----
         2  Ben        Carter    York
         6  Farid      Khan      York

With GRANT SELECT ON dbo.CustomerDirectory TO SalesTeam; the SalesTeam role can read the directory without any permission on dbo.Customers. Logins, roles and permissions are covered in the Master level module.

Quick check

What does a view store?

Quick check

A view shows products with Price < 10, without CHECK OPTION. You set a product’s price to 15 through the view. What happens?

Practice

Create a view dbo.CategoryStock with one row per category that has products: the category Name as Category, the number of products as Products, and the value of the stock (Price × Stock, added up) as StockValue. Then use it to list the categories whose stock is worth more than 1,000, the most valuable first.

Show a hint

GROUP BY inside the view, with an alias for each expression. The ORDER BY and the 1,000 filter go in the query that reads the view.

Show the solution

Solution.sql

1CREATE OR ALTER VIEW dbo.CategoryStock2AS3SELECT c.Name AS Category,4       COUNT(*) AS Products,5       SUM(p.Price * p.Stock) AS StockValue6FROM dbo.Products AS p7JOIN dbo.Categories AS c ON c.CategoryId = p.CategoryId8GROUP BY c.Name;9GO10SELECT Category, Products, StockValue11FROM dbo.CategoryStock12WHERE StockValue > 100013ORDER BY StockValue DESC;

Output

Category  Products  StockValue
--------  --------  ----------
Timber           2     6760.00
Building         2     3152.00
Tools            3     2490.50
Fixings          2     2235.00
Paint            2     1725.00

Remember

A view is a saved SELECT that you query like a table; it stores no rows and always shows current data.
Use CREATE OR ALTER VIEW, in a batch of its own, and give every column a name.
ORDER BY is not allowed in a view: sort in the outer query. TOP 100 PERCENT … ORDER BY does not order anything.
SELECT * in a view is expanded once, at creation; list the columns instead.
You can change data through a view when the change touches one base table; the view’s WHERE limits which rows are reached.
WITH CHECK OPTION rejects changes that would move a row out of the view (Msg 550).
WITH SCHEMABINDING stops the base columns being changed while the view exists, and is required for indexed views.
Views can hide columns and rows: grant permission on the view instead of the table.

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Views

Every column needs a name

No ORDER BY inside a view

The SELECT * trap

Changing data through a view

Updating through a view

SCHEMABINDING: protect what the view depends on

Views for security

Remember