Select Top n[Percent]...... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Top n[Percent]......

Author

Topic

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2007-11-13 : 08:46:29

Hi all
As you are aware we have TOP n [Percent] keyword that is used in Select statements ,I have a question??
When this keyword is evaluated by Sql-Server, after retreiving all Query-Result or upon the number of results reach to the specified number,sql-server stops
retrieving the rest of record.?

Thanks in advance.
regards.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-13 : 11:34:44

If I understand your question correctly, the answer is the former, i.e. after retreiving all Query-Result.

I learnt that from this book: Inside Microsoft SQl Server 2005: T-SQL Querying by Itzik Ben-Gan. Chapter one Logical Query Processing.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-13 : 11:47:25

It retrieves the entire result set matching the conditions and then selects the TOP X from it.. if there is an ORDER BY it has to sort the result set and then fetches the TOP X. SET ROWCOUNT X works the other way.. as soon as the first X records are retrieved it stops..If there is no ORDER BY then SET ROWCOUNT X may be a better option but you have to remember to set it back to 0 immediately after your query. Also, just FYI the SET ROWCOUNT is being depricated in 2008.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2007-11-14 : 13:02:11

quote:
[i].. as soon as the first X records are retrieved it stops..

Hi dinakar
Thanks 4 ur reply
So you mean if i have a SELECT statement with [ORDER BY] clause and if i use [Set RowCount n] command before it,
SQL SERVER ignores Sorting the Result Set and just retrieve the first[n] record of Un-Sorted Result set? yes?

Thanks in advance
Regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources