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 tablesonly 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):
CREATE VIEW myauthors AS SELECT TOP 100 PERCENT * FROM authors ORDER BY au_lname
The query in Listing 9-3 shows that ORDER BY is in effect when we issue a simple query against the view:
SELECT au_id, au_lname, au_fname FROM myauthors au_id au_lname au_fname ----------- ---------------------------------------- -------------------- 409-56-7008 Bennet Abraham 648-92-1872 Blotchet-Halls Reginald 238-95-7766 Carson Cheryl 722-51-5454 DeFrance Michel 712-45-1867 del Castillo Innes 427-17-2319 Dull Ann 213-46-8915 Green Marjorie 527-72-3246 Greene Morningstar 472-27-2349 Gringlesby Burt 846-92-7186 Hunter Sheryl 756-30-7391 Karsen Livia 486-29-1786 Locksley Charlene 724-80-9391 MacFeather Stearns 893-72-1158 McBadden Heather 267-41-2394 O'Leary Michael 807-91-6654 Panteley Sylvia 998-72-3567 Ringer Albert 899-46-2035 Ringer Anne 341-22-1782 Smith Meander 274-80-9391 Straight Dean 724-08-9931 Stringer Dirk 172-32-1176 White Johnson 672-71-3249 Yokomoto Akiko
Understand that the row order is still not guaranteed, even with ORDER BY in place. Parallel data gathering and other operations by SQL Server could cause the rows to be returned out of sequence. To guarantee the order, use an ORDER BY clause with the SELECT that queries the view.
Like stored procedures, the status of SET QUOTED_IDENTIFIER and SET ANSI_NULLS is saved with each view. This means that individual session settings for these options are ignored by the view when it's queried. It also means that you can localize special quoted identifier or NULL handling to a particular view without affecting anything else.
An UPDATE to the view without an INSTEAD OF trigger is not allowed to affect more than one underlying base table at a time. If the view joins two or more tables together, an UPDATE to it may only change one of them. Likewise, an INSERT into such a view must only modify one table at a time. This means that values can be supplied for only one tablethe columns in the other tables must have DEFAULT constraints, allow NULLs, or otherwise be optional. Unless an INSTEAD OF trigger is present, DELETE can only be used with single-table views. It can't be used with multitable views at all.