Microsoft® SQL Server® 2012 Bible

131

Chapter 6: Introducing Basic Query Flow

6

Qualifi ed Columns

A common problem with queries is that column names are duplicated in multiple tables.

Including the column in the select list by column names alone can cause an “ambiguous

column name” error. Basically, SQL Server complains that it doesn’t know which column

you refer to. Even if they contain the same exact data, SQL Server must know which col-

umn to select.

CREATE TABLE t1 (col1 INT);

CREATE TABLE t2 (col1 INT);

SELECT col1

FROM t1

CROSS JOIN t2;

Result:

Msg 209, Level 16, State 1, Line 2

Ambiguous column name 'col1'.

The solution, of course, is to qualify the column name by including the table:

SELECT t1.col1

FROM t1

CROSS JOIN t2;

Chapter 8 details how to build expressions that you can use as columns or in several other places

within the SELECT statement.

Ordering the Result Set ......................................................................................

Logically, relational data should always be considered an unordered list. The primary key’s

purpose is to uniquely identify the row, not sort the table. SQL Server usually returns the

data in the order of the primary key (because that’s probably the clustered index), but

there’s no logical guarantee of that order. The only correct way to sort the results is with an

ORDER BY clause.

SQL can sort by multiple columns, and the sort columns don’t have to be columns that are

returned by the SELECT, so there’s a lot of fl exibility in how you can specify the columns.

Using Management Studio’s Query Designer, you can create the ORDER BY by selecting the

sort order for the column, as shown in Figure 6-4.

c06.indd 131c06.indd 131 7/30/2012 4:16:05 PM7/30/2012 4:16:05 PM

http://www.it-ebooks.info