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:
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
|
No responses found. Be the first to respond and make money from revenue sharing program.
|