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