The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

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 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):

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

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:

Listing 9-3 Including ORDER BY in the view orders the result set.

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.

ANSI_NULLS and QUOTED_IDENTIFIER

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.

DML Restrictions

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.