Jerry Nixon @Work: The new syntax and applications of TOP in SQL 2005

Jerry Nixon on Windows

Friday, March 14, 2008

The new syntax and applications of TOP in SQL 2005

Top is a nice keyword. It lets you get only a few records from a query. But in 2005, there is new syntax that enables you to do a lot of what you WISH SQL 2000 did. Here's the syntax for you to review from my PASS presentation:

/*
TOP Select (simple)
*/

Select Top (15)
ProductName
,UnitPrice
From
Products

GO

/*
TOP PERCENT Select WITH TIES

Note: WITH TIES makes sure that any products with price equal to the lowest price
returned are also included in the result set, even if doing this exceeds 10 percent
*/

SET NOCOUNT ON

-- how many show be 10 percent?

Select Convert(real, Count(*))/Convert(real, 10) From Products

-- "tie" to NTILE calculations

Select Top (10) PERCENT
ProductName
,UnitPrice
From
Products
Order By
UnitPrice Desc

GO

Select Top (10) PERCENT WITH TIES
ProductName
,UnitPrice
From
Products
Order By
UnitPrice Desc

GO

/*
SET ROWCOUNT
*/

Declare @i int; Set @i = 5
SET ROWCOUNT @i

-- limit returned rows

Select
ProductName
,UnitPrice
From
Products

SET ROWCOUNT 0

GO

/*
TOP Expressions
*/

Declare @i int; Set @i = 5

-- use a variable

Select TOP (@i)
ProductName
,UnitPrice
From
Products

-- use an equation

Select TOP (10 - 4)
ProductName
,UnitPrice
From
Products