July 29, 2008 at 9:51 pm
Comments posted to this topic are about the item TOP and TABLESAMPLE
July 30, 2008 at 7:19 am
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
July 30, 2008 at 8:16 am
Correct answer: select top 1000 col1 from bigtable, select top 10 percent col1 from bigtable
It doesn't say to choose all correct statements!
July 30, 2008 at 8:20 am
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.
July 30, 2008 at 8:27 am
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?
July 30, 2008 at 9:04 am
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 ...
July 30, 2008 at 9:15 am
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..........
July 30, 2008 at 9:26 am
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
July 30, 2008 at 9:46 am
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.
July 30, 2008 at 9:47 am
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
July 30, 2008 at 9:53 am
No apology necessary - I always get them wrong when I am sure I know the answer. I learned something - that's more important than the points (frosting).
Jamie
July 30, 2008 at 10:12 am
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
I have edited my previous 2 posts to reflect the above.
Again - thanks for not kicking my butt too hard, but I deserved it
July 30, 2008 at 10:13 am
Finally found the source of confusion:
Which of the following statements is guaranteed to return 1000 rows
instead of
Which of the following statements are guaranteed to return 1000 rows
But the answer possibilities and checkboxes do not leave much room for confusion. (unless one ignores the last 3 answers, which is never a good idea.
Thanks for the very good question, keep it up!
Best Regards,
Chris Büttner
July 30, 2008 at 10:16 am
Not to cause a problem here but I think that I agree with bitbucket on this one. But there is more to this then a simple question and answer.
The QOTD has been an opportunity for many to learn a little something each day, as well it has been a reminder of what you may already know.
The question was to make a selection for the best answer. It clearly did not say select the best answer or answers, or choose all that apply. As a result we looked at the question as being straight forward. It was not.
The possible intent then of the question might not have been trying to get the right answer but to be deceptive in approach and to reinforce the negative. This also is a powerful tool in learning for you remember those professors and others who pose questions designed to make you fail. They generally are hated at the time and some are never appreciated but they teach us a much more important lesson then a simple sql construct or some class materials. They teach us that we need to be aware at all times no matter who or what we are dealing with, for even the trusted can lead you astray if you are not aware.
The lesson you are providing here is far more important then you know. For those who appear to be telling it like it is, may have an agenda to point you in their direction for their gain instead of pointing at the truth.
Thanks:)
Miles...
Not all gray hairs are Dinosaurs!
July 30, 2008 at 10:23 am
bitbucket - no worries - I was only trying to understand how best to help you - so no butt kicking here!
Another lesson learned for me too - must write questions without (too much) ambiguity 😀
Kev
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply