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 12»»

TOP and TABLESAMPLE Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2008 9:51 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:02 AM
Points: 2,684, Visits: 2,440
Comments posted to this topic are about the item TOP and TABLESAMPLE
Post #543173
Posted Wednesday, July 30, 2008 7:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
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
Post #543436
Posted Wednesday, July 30, 2008 8:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 14, 2011 5:21 AM
Points: 82, 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!
Post #543523
Posted Wednesday, July 30, 2008 8:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 5,603, Visits: 25,016
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
Post #543532
Posted Wednesday, July 30, 2008 8:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:02 AM
Points: 2,684, Visits: 2,440
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?
Post #543542
Posted Wednesday, July 30, 2008 9:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:30 PM
Points: 5,603, Visits: 25,016
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
Post #543584
Posted Wednesday, July 30, 2008 9:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:02 AM
Points: 2,684, Visits: 2,440
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..........

Post #543601
Posted Wednesday, July 30, 2008 9:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
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
Post #543615
Posted Wednesday, July 30, 2008 9:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,521, Visits: 3,039
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.
Post #543650
Posted Wednesday, July 30, 2008 9:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:02 AM
Points: 2,684, Visits: 2,440
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... :)

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
Post #543652
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse