Microsoft® SQL Server® 2012 Bible

133

Chapter 6: Introducing Basic Query Flow

6

FROM Person.Person

ORDER BY LastName, FirstName;

Result:

FirstName LastName

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

Syed Abbas

Catherine Abel

Kim Abercrombie

...

ORDER BY and the order of columns in the select list are completely independent.

Specifying the ORDER BY Using Expressions

In the case of sorting by an expression, you can repeat the entire expression in the

ORDER BY clause. This does not cause a performance hit because the SQL Server Query

Optimizer is smart enough to avoid recomputing the expression:

SELECT

LastName + ', ' + FirstName AS FullName

FROM Person.Person

ORDER BY LastName + ', ' + FirstName;

Result:

FullName

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

Abbas, Syed

Abel, Catherine

Abercrombie, Kim

...

Using an expression in the ORDER BY clause can solve some headaches. For example, some

database developers store product titles in two columns: One column includes the full title,

and the duplicate column stores the title stripped of the leading “The.” In terms of perfor-

mance, such denormalization might be a good idea, but using a case expression within the

ORDER BY clause correctly sorts without duplicating the title. (Chapter 8 covers the full

syntax for the CASE expression.)

The AdventureWorks sample database includes a list of Product Descriptions. If the

Description includes a leading “This,” then the CASE expression removes it from the

data and passes to the ORDER BY:

USE AdventureWorks;

SELECT Description, LEN(Description) AS TextLength

c06.indd 133c06.indd 133 7/30/2012 4:16:06 PM7/30/2012 4:16:06 PM

http://www.it-ebooks.info