Microsoft® SQL Server® 2012 Bible

130

Part II: Building Databases and Working with Data

Result:

ProductName OneYearSaleStartDate

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

Adjustable Race abc 2003-06-01 00:00:00.000

Bearing Ball abc 2003-06-01 00:00:00.000

BB Ball Bearing abc 2003-06-01 00:00:00.000

...

The fi rst column’s name is changed from Name to ProductName by means of an alias. The

second column is an expression without an alias, so it has no column name. A better prac-

tice is to name expression columns using an alias, as demonstrated in the third column.

Accidental aliases are a common source of errors. Take a careful look at the next query:

USE AdventureWorks;

SELECT

Name

'abc',

SellStartDate + 365 OneYearSellStartDate

FROM Production.Product;

Result:

abc OneYearSaleStartDate

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

Adjustable Race 2003-06-01 00:00:00.000

Bearing Ball 2003-06-01 00:00:00.000

BB Ball Bearing 2003-06-01 00:00:00.000...

The second column isn’t abc as in the previous query. Instead, because of a missing comma,

the 'abc' in the query became an accidental alias for the fi rst column.

What’s Wrong with Select *?

For some developers, using SELECT * is common practice. However, this method of programming can

pose a few challenges. If you talk to fi ve SQL Server DBAs, it is likely that you get fi ve different reasons

not to use the SELECT *. On the other hand, if you talk to fi ve SQL developers, they might give you fi ve

different reasons to use SELECT *. Basically you should avoid using it for several reasons, but if you are

ever faced with the challenge of validating, here are a few reasons why:

■ It is a waste of resources. In most cases more data is returned than needed.

■ If you use a SELECT * in your query, how do you build a covering index to satisfy the needs

of the query?

■ Adding a column to the table could potentially break the application.

These are only a few, but there are many more and they all are valid. Just a quick Internet search should

reveal a list that provides enough ammunition for any DBA.

c06.indd 130c06.indd 130 7/30/2012 4:16:04 PM7/30/2012 4:16:04 PM

http://www.it-ebooks.info