Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how top clause works Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 12:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 1:30 AM
Points: 113, Visits: 207
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.
Post #1407060
Posted Tuesday, January 15, 2013 1:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:56 AM
Points: 221, Visits: 1,692
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.
"
Post #1407062
Posted Tuesday, January 15, 2013 1:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1407071
Posted Tuesday, January 15, 2013 3:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #1407145
Posted Tuesday, January 15, 2013 4:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 14,802, Visits: 27,276
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1407155
Posted Tuesday, January 15, 2013 5:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #1407185
Posted Tuesday, January 15, 2013 5:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 14,802, Visits: 27,276
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1407188
Posted Tuesday, January 15, 2013 10:04 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
thanks grant

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1407585
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse