Microsoft® SQL Server® 2012 Bible

280

Part II: Building Databases and Working with Data

SELECT *

FROM dbo.vEmployeeList

ORDER BY LastName, FirstName

Result:

BusinessEntityID LastName FirstName JobTitle

---------------- ------------ ---------- -----------------------

285 Abbas Syed Pacific Sales Manager

38 Abercrombie Kim Production Technician - WC60

211 Abolrous Hazem Quality Assurance Manager

121 Ackerman Pilar Shipping and Receiving

Supervisor

If the view includes a TOP predicate, then the view is allowed to include an ORDER BY—

without the ORDER BY, the TOP would be meaningless. However, this ORDER BY clause

serves only to defi ne which rows qualify for the TOP predicate. The only way to logically

guarantee sorted results is to defi ne the ORDER BY clause in the executing query.

SQL Server 2000, and some service packs of SQL Server 2005, had a bug (yes, let’s call it a bug) in the Query

Optimizer that would enable an ORDER BY in a view using a top 100 percent predicate. This behavior was

never documented or offi cially supported. However, in SQL Server 2008, this error was corrected and the

TOP 100 percent with an ORDER BY trick does not sort the result.

A source of confusion is that Management Studio’s Query Designer enables views to have sorted columns, and it

adds the TOP 100 percent trick to the view. That is a SQL Server 2008 “bug.”

View Restrictions

Although a view can contain nearly any valid SELECT statement, a few basic restrictions do

apply:

■ (^) Views may not include the SELECT INTO option that creates a new table from the
selected columns. SELECT INTO fails if the table already exists and it does not
return any data, so it’s not a valid view:
SELECT * INTO Table
■ (^) Views may not refer to a temporary table (one with a # in the name) or a table vari-
able (preceded with an @) because these types of tables are transient.
■ (^) The OPTION clause, which gives table or query hints for the entire query, is not
allowed.
■ (^) The tablesample table option, which can randomly select pages, is not allowed
within a view.
c11.indd 280c11.indd 280 7/30/2012 4:41:10 PM7/30/2012 4:41:10 PM
http://www.it-ebooks.info