Table Expressions in Microsoft SQL Server 2008 T-SQL

Views

The two types of table expressions discussed so far—derived tables and CTEs—have a very limited scope, which is the single statement scope. As soon as the outer query against those table expressions is finished, they are gone. This means that derived tables and CTEs are not reusable.

Views and inline table-valued functions (inline TVFs) are two reusable types of table expressions; their definition is stored as a database object. Once created, those objects are permanent parts of the database and are only removed from the database if explicitly dropped.

In most other respects, views and inline TVFs are treated like derived tables and CTEs. For example, when querying a view or an inline TVF, SQL Server expands the definition of the table expression and queries the underlying objects directly, as with derived tables and CTEs.

In this section, I’ll describe views; in the next section, I’ll describe inline TVFs. As I mentioned earlier, a view is a reusable table expression whose definition is stored in the database. For example, the following code creates a view called USACusts in the Sales schema in the TSQLFundamentals2008 database, representing all customers from the United States:

USE TSQLFundamentals2008;
IF OBJECT_ID('Sales.USACusts') IS NOT NULL
  DROP VIEW Sales.USACusts;
GO
CREATE VIEW Sales.USACusts
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO 

Note that just as with derived tables and CTEs, instead of using inline column aliasing as shown in the preceding code, you can use external column aliasing by specifying the target column names in parentheses immediately after the view name.

Once you create this view, you can query it much like you query other tables in the database:

SELECT custid, companyname
FROM Sales.USACusts;

Because a view is an object in the database, you can control access to the view with permissions just like other objects that can be queried (for example, SELECT, INSERT, UPDATE, and DELETE permissions). For example, you can deny direct access to the underlying objects while granting access to the view.

Note that the general recommendation to avoid using SELECT * has specific relevance in the context of views. The columns are enumerated in the compiled form of the view and new table columns will not be automatically added to the view. For example, suppose you define a view based on the query SELECT * FROM dbo.T1, and at the view creation time the table T1 has the columns col1 and col2. SQL Server stores information only on those two columns in the view’s metadata. If you alter the definition of the table adding new columns, those new columns will not be added to the view. You can refresh the view’s metadata using a stored procedure called sp_refreshview, but to avoid confusion, the best practice is to explicitly list the column names that you need in the definition of the view. If columns are added to the underlying tables and you need them in the view, use the ALTER VIEW statement to revise the view definition accordingly.

Views and the ORDER BY Clause

The query that you use to define a view must meet all requirements mentioned earlier with respect to table expressions in the context of derived tables. The view should not guarantee any order to the rows, all view columns must have names, and all column names must be unique. In this section, I’ll elaborate a bit about the ordering issue, which is a fundamental point that is crucial to understand.

Remember that a presentation ORDER BY clause is not allowed in the query defining a table expression because there’s no order among the rows of a relational table. An attempt to create an ordered view is absurd because it violates fundamental properties of a relation as defined by the relational model. If you need to return rows from a view sorted for presentation purposes, you shouldn’t try to make the view something it shouldn’t be. Instead, you should specify a presentation ORDER BY clause in the outer query against the view, like so:

SELECT custid, companyname, region
FROM Sales.USACusts
ORDER BY region;

Try running the following code to create a view with a presentation ORDER BY clause:

ALTER VIEW Sales.USACusts
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
ORDER BY region;
GO 

This attempt fails and you get the following error:

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

The error message indicates that SQL Server allows the ORDER BY clause in two exceptional cases—when the TOP or FOR XML options are used. Neither case follows the SQL standard, and in both cases the ORDER BY clause serves a purpose beyond the usual presentation purpose.

Because T-SQL allows an ORDER BY clause in a view when TOP is also specified, some people think that they can create “ordered views” by using TOP (100) PERCENT like so:

ALTER VIEW Sales.USACusts
AS

SELECT TOP (100)
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax

FROM Sales.Customers
WHERE country = N'USA'
ORDER BY region;
GO 

Even though the code is technically valid and the view is created, you should be aware that because the query is used to define a table expression, the ORDER BY clause here is only guaranteed to serve the logical filtering purpose for the TOP option. If you query the view and don’t specify an ORDER BY clause in the outer query, presentation order is not guaranteed.

For example, run the following query against the view:

SELECT custid, companyname, region
FROM Sales.USACusts;

Here is the output from one of my executions showing that the rows are not sorted by region:

custid      companyname             region
----------- ----------------------- ---------------
32          Customer YSIQX          OR
36          Customer LVJSO          OR
43          Customer UISOJ          WA
45          Customer QXPPT          CA
48          Customer DVFMB          OR
55          Customer KZQZT          AK
65          Customer NYUHS          NM
71          Customer LCOUJ          ID
75          Customer XOJYP          WY
77          Customer LCYBZ          OR
78          Customer NLTYP          MT
82          Customer EYHKM          WA
89          Customer YBQTI          WA

In some cases a query that is used to define a table expression has the TOP option with an ORDER BY clause, and the query against the table expression doesn’t have an ORDER BY clause. In those cases, therefore, the output might or might not be returned in the specified order. If the results happen to be ordered, it may be due to optimization reasons, especially when you use values other than TOP (100) PERCENT. The point I’m trying to make is that any order of the rows in the output is considered valid, and no specific order is guaranteed; therefore, when querying a table expression, you should not assume any order unless you specify an ORDER BY clause in the outer query.

Do not confuse the behavior of a query that is used to define a table expression with a query that isn’t. A query with TOP and ORDER BY does not guarantee presentation order only in the context of a table expression. In the context of a query that is not used to define a table expression, the ORDER BY clause serves both the logical filtering purpose for the TOP option and the presentation purpose.

View Options

When you create or alter a view, you can specify view attributes and options as part of the view definition. In the header of the view under the WITH clause you can specify attributes such as ENCRYPTION and SCHEMABINDING, and at the end of the query you can specify WITH CHECK OPTION. The following sections describe the purpose of these options.

The ENCRYPTION Option

The ENCRYPTION option is available when you create or alter views, stored procedures, triggers, and user-defined functions (UDFs). The ENCRYPTION option indicates that SQL Server will internally store the text with the definition of the object in an obfuscated format. The obfuscated text is not directly visible to users through any of the catalog objects—only to privileged users through special means.

Before you look at the ENCRYPTION option, run the following code to alter the definition of the USACusts view to its original version:

ALTER VIEW Sales.USACusts
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO 

To get the definition of the view, invoke the OBJECT_DEFINITION function like so:

SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));

The text with the definition of the view is available because the view was created without the ENCRYPTION option. You get the following output:

CREATE VIEW Sales.USACusts
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'; 

Next, alter the view definition—only this time, include the ENCRYPTION option:

ALTER VIEW Sales.USACusts WITH ENCRYPTION
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax

FROM Sales.Customers
WHERE country = N'USA';
GO 

Try again to get the text with the definition of the view:

SELECT OBJECT_DEFINITION(OBJECT_ID('Sales.USACusts'));

This time you get a NULL back.

As an alternative to the OBJECT_DEFINITION function, you can use the sp_helptext stored procedure to get object definitions. The OBJECT_DEFINITION function was added in SQL Server 2005 while sp_helptext was also available in earlier versions. For example, the following code requests the object definition of the USACusts view:

EXEC sp_helptext 'Sales.USACusts';

Because in our case the view was created with the ENCRYPTION option, you will not get the object definition back, but the following message:

The text for object 'Sales.USACusts' is encrypted.

The SCHEMABINDING Option

The SCHEMABINDING option is available to views and UDFs, and it binds the schema of referenced objects and columns to the schema of the referencing object. It indicates that referenced objects cannot be dropped and that referenced columns cannot be dropped or altered.

For example, alter the USACusts view with the SCHEMABINDING option:

ALTER VIEW Sales.USACusts WITH SCHEMABINDING
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO

Now try to drop the Address column from the Customers table:

ALTER TABLE Sales.Customers DROP COLUMN address;

You get the following error:

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

Without the SCHEMABINDING option, such a schema change would have been allowed, as well as dropping the Customers table altogether. This can lead to errors at run time when you try to query the view, and referenced objects or columns that do not exist. If you create the view with the SCHEMABINDING option, you can avoid these errors.

The object definition must meet a couple of technical requirements to support the SCHEMABINDING option. The query is not allowed to use * in the SELECT clause; instead, you have to explicitly list column names. Also, you must use schema-qualified two-part names when referring to objects. Both requirements are actually good practices in general.

As you can imagine, creating your objects with the SCHEMABINDING option is a good practice.

The Option CHECK OPTION

The purpose of CHECK OPTION is to prevent modifications through the view that conflict with the view’s filter—assuming that one exists in the query defining the view.

The query defining the view USACusts filters customers where the country attribute is equal to N’USA’. The view is currently defined without CHECK OPTION. This means that you can currently insert rows through the view with customers from countries other than the United States, and you can update existing customers through the view, changing their country to one other than the United States. For example, the following code successfully inserts a customer with company name Customer ABCDE from the United Kingdom through the view:

INSERT INTO Sales.USACusts(
  companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax)
 VALUES(
  N'Customer ABCDE', N'Contact ABCDE', N'Title ABCDE', N'Address ABCDE',
  N'London', NULL, N'12345', N'UK', N'012-3456789', N'012-3456789'); 

The row was inserted through the view into the Customers table. However, because the view filters only customers from the United States, if you query the view looking for the new customer you get an empty set back:

SELECT custid, companyname, country
FROM Sales.USACusts
WHERE companyname = N'Customer ABCDE';

Query the Customers table directly looking for the new customer:

SELECT custid, companyname, country
FROM Sales.Customers
WHERE companyname = N'Customer ABCDE';

You get the customer information in the output, because the new row made it to the Customers table:

custid      companyname        country
----------- ------------------ ---------------
92          Customer ABCDE     UK

Similarly, if you update a customer row through the view, changing the country attribute to a country other than the United States, the update makes it to the table. But that customer doesn’t show up anymore in the view because it doesn’t qualify to the view’s query filter.

If you want to prevent modifications that conflict with the view’s filter, add WITH CHECK OPTION at the end of the query defining the view:

ALTER VIEW Sales.USACusts WITH SCHEMABINDING
AS

SELECT
  custid, companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA'
WITH CHECK OPTION;
GO

Now try to insert a row that conflicts with the view’s filter:

INSERT INTO Sales.USACusts(
  companyname, contactname, contacttitle, address,
  city, region, postalcode, country, phone, fax)
 VALUES(
  N'Customer FGHIJ', N'Contact FGHIJ', N'Title FGHIJ', N'Address FGHIJ',
  N'London', NULL, N'12345', N'UK', N'012-3456789', N'012-3456789'); 

You get the following error:

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.

When you’re done, run the following code for cleanup:

DELETE FROM Sales.Customers
WHERE custid > 91;

DBCC CHECKIDENT('Sales.Customers', RESEED, 91);

IF OBJECT_ID('Sales.USACusts') IS NOT NULL DROP VIEW Sales.USACusts;