Microsoft® SQL Server® 2012 Bible

137

Chapter 6: Introducing Basic Query Flow

6

Terminating the Statement

ANSI SQL uses a semicolon to terminate a statement. Although it’s been there as an option for several

versions, code with semicolons was unheard of in the SQL Server community until recently. SQL Server

2005 began requiring it for some commands. Therefore, following are the rules about semicolons.

When semicolons are required:

■ (^) At the end of the statement preceding a common table expression (CTE)
■ (^) At the end of a MERGE statement
When not to use a semicolon:
■ (^) Between the END TRY and BEGIN CATCH.
■ (^) Between the IF condition and the BEGIN.
■ (^) Don’t mix GO and the semicolon on the same line.

Select Distinct ...................................................................................................

The fi rst predicate option in the SELECT command is the keyword DISTINCT, which elimi-

nates duplicate rows from the result set of the query. The duplications are based only on

the output columns, not the underlying tables. The opposite of DISTINCT is ALL. Because

ALL is the default, it is typically not included.

The following example demonstrates the difference between DISTINCT and ALL. Joins are

explained in Chapter 9 but here the JOIN between product and salesorderdetails

generates a row each time a product is sold as part of an order. Because this select state-

ment returns only the productname column, it’s a perfect example of duplicate rows for

the DISTINCT predicate:

USE AdventureWorks;

SELECT ALL p.Name

FROM Production.Product p

INNER JOIN Sales.SalesOrderDetail so

ON p.ProductID = so.ProductID

Result:

Name

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

Sport-100 Helmet, Red

Sport-100 Helmet, Red

Sport-100 Helmet, Red

Sport-100 Helmet, Red

Sport-100 Helmet, Red

Sport-100 Helmet, Red

Sport-100 Helmet, Red

c06.indd 137c06.indd 137 7/30/2012 4:16:07 PM7/30/2012 4:16:07 PM

http://www.it-ebooks.info