how top clause works

Question

Post reply

how top clause works

sej2008

SSCarpal Tunnel

Points: 4495
More actions
January 15, 2013 at 12:56 am

#287957

I found that TOP clause works little randomly with
Select TOP 50 percent * from Tablename as it is not giving exact 50 percent in my result set .how it gets the data reading from data pages.I have Idea that it is little random in working in respect to percent clause over datapages.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

e4d4 SSCertifiable Points: 5777 More actions · Answer 1

http://msdn.microsoft.com/en-us/library/ms189463.aspx

" PERCENT

Indicates that the query returns only the first expression percent of rows from the result set. Fractional values are rounded up to the next integer value.

"

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

sej2008 (1/15/2013)
I found that TOP clause works little randomly with
Select TOP 50 percent * from Tablename as it is not giving exact 50 percent in my result set .how it gets the data reading from data pages.I have Idea that it is little random in working in respect to percent clause over datapages.

What happens when you add ORDER BY to your query?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

sej2008 (1/15/2013)
I found that TOP clause works little randomly with
Select TOP 50 percent * from Tablename as it is not giving exact 50 percent in my result set .how it gets the data reading from data pages.I have Idea that it is little random in working in respect to percent clause over datapages.

sql server never gives guarantee about the order of data until/unless ORDER BY clause is mentioned. see http://msdn.microsoft.com/en-us/library/ms188385.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

Yes, it's going to be a little bit random in the number of rows it returns. However, for a given set of inputs against a constant set of data, you should see consistent results. But as soon as the data changes or the inputs change, all bets are off.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Grant Fritchey (1/15/2013)
as soon as the data changes or the inputs change, all bets are off.

yes i have also realized that for smaller set , we can see the consistent data more often but what make it unexpected or randomly ordered for larger set (for the time being, we can keep out parallelism,out of site here ) ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 6

Bhuvnesh (1/15/2013)
Grant Fritchey (1/15/2013)
as soon as the data changes or the inputs change, all bets are off.
yes i have also realized that for smaller set , we can see the consistent data more often but what make it unexpected or randomly ordered for larger set (for the time being, we can keep out parallelism,out of site here ) ?

Random ordering will absolutely not occur unless you are not using an ORDER BY statement. Then, it's all over Microsoft documentation and the web, there's no guarantee for data order without an ORDER BY statement. It's that simple. Any number of things can affect the order of the data returned, page splits and rearrange, different execution plans based on changing statistics or changing parameters, more. If you don't use an ORDER BY, you can't be surprised when the order is different from one time to the next.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

thanks grant

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)