Microsoft® SQL Server® 2012 Bible

141

Chapter 6: Introducing Basic Query Flow

6

FROM Production.Product

ORDER BY ListPrice DESC

Result:

ProductNumber Name ListPrice SellStartDate

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

BK-R93R-62 Road-150 Red, 62 3578.27 07/01/05

BK-R93R-44 Road-150 Red, 44 3578.27 07/01/05

BK-R93R-48 Road-150 Red, 48 3578.27 07/01/05

BK-R93R-52 Road-150 Red, 52 3578.27 07/01/05

BK-R93R-56 Road-150 Red, 56 3578.27 07/01/05

BK-M82S-38 Mountain-100 Silver, 38 3399.99 07/01/05

BK-M82S-38 Mountain-100 Silver, 38 3399.99 07/01/05

BK-M82S-38 Mountain-100 Silver, 38 3399.99 07/01/05

BK-M82S-38 Mountain-100 Silver, 38 3399.99 07/01/05

If you move from Access to SQL Server, be aware that Access, by default, automatically adds the WITH TIES option

to the TOP() predicate.

An alternative to TOP() is the SET ROWCOUNT command, which limits any DML command to affecting only n num-

ber of rows until it’s turned off with SET ROWCOUNT 0. The issue is that ROWCOUNT isn’t portable either, and it’s

been deprecated for INSERT, UPDATE, and DELETE in SQL Server 2008.

Selecting a Random Row

There are times when you need a single random row. You can use this technique when pop-

ulating a table with random names.

Using the TOP(1) predicate returns a single row, and sorting the result set by newid()

randomizes the sort. Together they return a random row each time the query executes.

There is a performance cost to using TOP(1) and newid(). SQL Server has to add a

uniqueidentifier to every row and then sort by the uniqueidentifier. An elegant

solution is to add a tablesample option to the table when randomly selecting a single row

from a large table. Tablesample works by randomly selecting pages within the table and

then returning every row from those pages from the FROM clause:

USE AdventureWorks;

SELECT TOP(1) LastName

FROM Person.Person TableSample (10 Percent)

ORDER BY NewID();

c06.indd 141c06.indd 141 7/30/2012 4:16:09 PM7/30/2012 4:16:09 PM

http://www.it-ebooks.info