Difference between TOP clause in SQL 2000 and SQL 2005

Generally TOP clause is used to perform SELECT on top n results. This feature of TOP is extended in SQL 2005 so that we can also use expression apart from int, bigint and percent to perform query and also extended to be used in UPDATE and DELETE statements.

In SQL 2000

syntax: select Top N [Percent]

EX:

select Top 10 * from TableName

or

select Top 10 Percent * from TableName

n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return

Drawbacks:

We could not parameterize.

It will work only for select statements.

If we want to restrict the number of rows affected by a select at runtime, or restrict the rows affected by an update or delete you had to explicitly describe the rows using a join or where clause, or you could cheat a bit by using ROWCOUNT, like this

set rowcount 10 delete from table where payratefieldname=1 set rowcount 0

It will work but the risk is if for some reason rowcount is not set to 0 then the other statements will also restricted to 10.

All these drawbacks are overcome in SQL 2005 by introducing Expression in syntax.

In SQL 2005

syntax: select Top (Expression) [Percent]

EX:

Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

Ex:

select Top 10 * from TableName

or

select Top 10 Percent * from TableName

or we can set at runtime as

Declare @ int

SET @topNum = 10

select TOP (@topNum) * from

For select statements you must specify parentheses if you are passing a parameter, otherwise they are optional

Select Top (@topNum) * from TableName Select Top 10 * from TableName

When doing an update or delete, you have to use the parentheses in both cases:

Delete Top (@topNum) from employeesDelete Top (10) from TableName update Top (@topNum) TableName set fieldname = @fieldvalue  update Top (10) from employees set fieldname = @fieldvalue This is nice tricky change introduced in SQL 2005