Microsoft® SQL Server® 2012 Bible

302

Part II: Building Databases and Working with Data

Results:

BusinessEntityID LastName FirstName

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

285 Abbas Syed

293 Abel Catherine

295 Abercrombie Kim

2170 Abercrombie Kim

38 Abercrombie Kim

211 Abolrous Hazem

2357 Abolrous Sam

I recommend that you manually build tables, or at least carefully check the data structures

created by SELECT/INTO.

SELECT/INTO can serve many useful functions:

■ If zero rows are selected from a table, then SELECT/INTO creates a new table with

only the data schema (although with the limitations listed earlier).

■ If SELECT reorders the columns, or includes the cast() function, then the new

table retains the data within a modifi ed data schema.

■ When combined with a UNION query, SELECT/INTO can vertically combine data

from multiple tables. The INTO goes in the fi rst SELECT statement of a UNION

query.

■ (^) SELECT/INTO is especially useful for denormalizing tables. The SELECT statement
can pull from multiple tables and create a new fl at-fi le table.
There’s one caveat concerning SELECT/INTO and development style: The SELECT/INTO statement should not
replace the use of joins or views. When the new table is created, it’s a snapshot in time — a second copy of the data.
Databases containing multiple copies of old data sets are a sure sign of trouble. If you need to denormalize data for
ad hoc analysis, or to pass to a user, then creating a view is likely a better alternative.

Updating Data

SQL’s UPDATE command is an incredibly powerful tool. What used to take dozens of lines

of code with multiple nested loops now takes a single statement. Even better, SQL is not

a true language — it’s a declarative language. The SQL code is describing to the Query

Optimizer only what you want to do. The Query Optimizer then develops a cost-based, opti-

mized query execution plan to accomplish the task. It determines which tables to fetch

and in which order, how to merge the joins, and which indexes to use. It does this based on

several factors, including the current data-population statistics, the indexes available, how

they relate to the data population within the table, and table sizes. The Query Optimizer

c12.indd 302c12.indd 302 7/30/2012 4:42:39 PM7/30/2012 4:42:39 PM

http://www.it-ebooks.info