Restrictions

for RuBoard

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.

for RuBoard