Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

Restrictions

Transact-SQL doesn't support temporary views, although you can create static views in tempdb and achieve a similar effect. A derived table is also an approximation of a temporary view, as is a table variable with the results of a SELECT statement stored in it.

Views aren't allowed to reference temporary tables—only references to other views or permanent base tables are allowed.

As a rule, ORDER BY is not allowed in views, so the following syntax is not valid:

— _Not_ valid Transact-SQL syntax
CREATE VIEW myauthors AS
SELECT * FROM authors
ORDER BY au_lname

There is, however, a workaround. You can use Transact-SQL's TOP extension to allow ORDER BY in views, like this (Listing 9-2):

Listing 9-2. SELECT TOP can work around the ORDER ...