SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TOP and TABLESAMPLE


TOP and TABLESAMPLE

Author
Message
kevriley
kevriley
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3108 Visits: 2616
Comments posted to this topic are about the item TOP and TABLESAMPLE
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3127 Visits: 836
Other than the example given in the answer, where the set rowcount is turned on, what would be the situation where the top 1000 in a 10000 row table would not be returned? If I understand correctly, Microsoft has allowed someone to set rowcount and has no way to turn it off other than set rowcount off?

Thus using the familiar method, to guarantee 1000 records the statement would always need to be
set rowcount 0
go
select top 1000 col1 from mytable

Yes? Somehow this seems convoluted. I suspect we need to learn the tablesample method and forget the word TOP exists? Why does this seem convoluted?

Gotta say I learned something else with this question as well as the above. I've been having trouble using order by in a sub-query. Apparently, the set rowcount will allow me to order the sub-query prior to grabbing it. Not sure how much it helps as it is hard to know how many rows you will grab before you grab them.

Jamie
jon.hart
jon.hart
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 24
Correct answer: select top 1000 col1 from bigtable, select top 10 percent col1 from bigtable
It doesn't say to choose all correct statements!
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7771 Visits: 25280
DISREGARD THIS RANTING
Correct answer: select top 1000 col1 from bigtable, select top 10 percent col1 from bigtable


I too selected a single answer not multiple answers

KevRiley - please accept my sincere apologies for my stupidity


Sorry but I can NOT agree with the supposed correct answer

From SQL Server 2005 BOL (September 2007)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8868e8fd-6c42-4171-9eab-a0e38cb1bfd3.htm

When a number of rows is specified, instead of a percentage based on the total number of rows in the table, that number is converted into a percentage of the rows and, therefore, pages that should be returned. The TABLESAMPLE operation is then performed with that computed percentage.

The following example returns approximately 100 rows. The actual number of rows that are returned can vary significantly. If you specify a small number, such as 5, you might not receive results in the sample.

Copy Code
USE AdventureWorks ;
GO
SELECT FirstName, LastName
FROM Person.Contact
TABLESAMPLE (100 ROWS) ;

Further on the same BOL page
The Person.Contact table contains 19,972 rows. The following example returns approximately 10 percent of the rows. The number of rows returned usually changes every time that the statement is executed.

Copy Code
USE AdventureWorks ;
GO
SELECT FirstName, LastName
FROM Person.Contact
TABLESAMPLE (10 PERCENT) ;


Emphasis on approximately added by myself.

Logically then a specific number of rows is converted to a percentage and for a percentage the number of rows returned is approximately the number specified.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
kevriley
kevriley
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3108 Visits: 2616
bitbucket - I don't get what you are saying. Why don't you agree with the 'supposed' correct answer?

The quotes you provide, are exactly what I am saying with the question - TABLESAMPLE will return approximately the specified number of rows - not a guaranteed number.

Do you see what I mean?
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7771 Visits: 25280
DISREGARD THIS RANTING
Correct answer: select top 1000 col1 from bigtable, select top 10 percent col1 from bigtable


I too selected a single answer not multiple answers

KevRiley - please accept my sincere apologies for my stupidity


KevRiley the question as copied and pasted is:
You have a default standard SQL 2005 SP2 server. There is a table BigTable (col1 varchar(50)) with 10,000 rows. Which of the following statements is guaranteed to return 1000 rows


Emphasis on quaranteed added by myself.
quaranteed to me means 1,000 NOT maybe more or less ... but EXACTLY 1,000. is why I am

whle you are now saying
The quotes you provide, are exactly what I am saying with the question - TABLESAMPLE will return approximately the specified number of rows - not a guaranteed number.
Seems like the question has been re-worded but enough of this quibbling ...

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
kevriley
kevriley
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3108 Visits: 2616
Sorry, maybe I am missing something, or looking too deep into this but I still don't understand what the issue is.

I asked which statements would guarantee 1000 rows, the answer is the ones with the TOP clause, TABLESAMPLE isn't guaranteed.

I haven't re-worded the question , I wouldn't know how to after it has been published anyway!

Everything you say or quote about TABLESAMPLE is correct; you seem to be disagreeing with soemthing that hasn't been said


Please help me try to understand what is going on here..........
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3127 Visits: 836
Not sure if it helps explain the confusion or not, but I initially thought that top 1000 was an incorrect answer becasue it was the one I selected and I got the question wrong.

What I failed to notice was that the answer included checkboxes and not option buttons. Thus, I assumed that "top 1000" was not correct - found the reason why (Set Rowcount 100 GO Select top 1000 * from bigtable only gives you 100 so no guarantee). In fact, it is a correct answer and to be fully correct , you have to also choose the "top 10 percent" answer. Sometimes it helps to add the words, "choose all correct answers" to the question.

Jamie
john.arnott
john.arnott
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1958 Visits: 3059
I humbly accept my "Wrong Answer" status and thank you, Kev, for the lesson. I chose only "Top 1000..." as I had thought that "TOP n PERCENT" and "TABLESAMPLE..." were both imprecise in determining the number of rows returned. Your answer (and the MS doc) show "TOP n Percent" really works.

This was a good QOTD for me in that it taught me about that specific difference.
kevriley
kevriley
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3108 Visits: 2616
OK - fair point - subtle difference between checkboxes and radio buttons - I'll make any future questions very explicit!
I thought it read ok with 'which of the following statements' rather than 'which one of the following statements' ... after all I am a pedantic DBA... Smile

BUT....bitbucket said

Sorry but I can NOT agree with the supposed correct answer


which suggests it's not the wording or the structure of the question that is in dispute but the question and the given answers - which is something I still don't understand......


Kev
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search