Search
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Fri, 01-Aug-2014 Authors
All Time Authors
Sourav.Kayal
39750
Sheonarayan
37300
Niladri.Biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads

Difference between TOP clause in SQL 2000 and SQL 2005

Chinnu21
Posted by under Sql Server category on for Intermediate level | Views : 12789 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
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
Page copy protected against web site content infringement by Copyscape
About the Author

Chinnu21

Full Name: Anil Kumar Reddy Perugu
Member Level: Starter
Member Status: Member
Member Since: 4/27/2008 10:49:55 PM
Country:

http://anil.myfunda.net

Login to vote for this post.
Found interesting? Add this to:


Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)