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.

Introduction


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:


1) We could not parameterize.
2) 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


Conclusion


This is nice tricky change introduced in SQL 2005

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.