InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 16:54:29 GMT20http://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxWow... although I've not needed to use it, I've heard about TableSample before. Didn't know it would return a random number of rows in the rows mode. What a POS command it is. If you use (10 rows) on a 10,000 row table, you get 0 rows. The guys that put that on together need a porkchop dinner Moden style! :PWed, 10 Sep 2008 19:58:03 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxI've been caught out by the multiple answer thang before - but never again!This argument over lost points is hiding how good a question this is. Who at MS decided that a tablesample of 1000 rows should return *approximately* 1000 rows! I can see that 10 percent might be an approximation - but an exact number of rows! Sheesh.From BOL:[quote]When a number of rows is specified, rather than a percentage based on the total number of rows in the table, the number is converted into a percentage of the rows, and therefore, pages, that should be returned. The TABLESAMPLE is then performed with that computed percentage.[/quote]This is silly.Fri, 08 Aug 2008 03:59:18 GMTMelvillehttp://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxAs an FYI, I'm not upset about the complaints. I completely understand how it looks from your side and I apologize.The QOD is a hard place to get a good question written. And it's hard to think about all the ways things can be interpreted. You have valid reasons for not liking the question and I hope we've fixed them for people answering now and we'll keep them in mind in the future.Wed, 30 Jul 2008 12:08:44 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxOK, this is more my fault than Kevin's. To start, "is" changed to "are" and "select all that apply" added. I should have done that.The engine is built to show radio buttons if one answer is required, checkboxes if 2 or more are correct. That's how the MS tests work, but it also means there's a trick. If I forget the "select all the apply", you have to know that at least 2 answers are required. I'm not sure if I'd always like to have checkboxes, though that would remove the confusion from questions.I also can't an an either/or for partial credit. Enhancement is needed here, but I'm not sure how I can do this without confusing myself more often than not:w00t:I thought it was a good question, but I didn't read into it like a few of you. I also see it from a different perspective, so apologies for any confusion. The intent from me, and I'm guessing Kevin, was not to confuse, but show that SAMPLE isn't necessarily accurate.Wed, 30 Jul 2008 11:30:27 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxbitbucket - 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 :DKevWed, 30 Jul 2008 10:22:59 GMTkevrileyhttp://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxNot 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...Wed, 30 Jul 2008 10:16:07 GMTMiles Nealehttp://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxFinally found the source of confusion:[quote]Which of the following statements [u]is[/u] guaranteed to return 1000 rows[/quote]instead of[quote]Which of the following statements [u]are[/u] guaranteed to return 1000 rows[/quote]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!Wed, 30 Jul 2008 10:13:43 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspx[b]DISREGARD THIS RANTING [/b][quote]Correct answer: select top 1000 col1 from bigtable, select top 10 percent col1 from bigtable [/quote]I too selected a single answer not multiple answers[b]KevRiley - please accept my sincere apologies for my stupidity [/b]I have edited my previous 2 posts to reflect the above.Again - thanks for not kicking my butt too hard, but I deserved itWed, 30 Jul 2008 10:12:15 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxNo 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).Wed, 30 Jul 2008 09:53:29 GMTJamie Longstreet-481950http://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxOK - 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[quote]Sorry but I can NOT agree with the supposed correct answer[/quote]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......KevWed, 30 Jul 2008 09:47:54 GMTkevrileyhttp://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxI 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.Wed, 30 Jul 2008 09:46:32 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxNot 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.Wed, 30 Jul 2008 09:26:16 GMTJamie Longstreet-481950http://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxSorry, 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 saidPlease help me try to understand what is going on here..........Wed, 30 Jul 2008 09:15:35 GMTkevrileyhttp://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspx[b]DISREGARD THIS RANTING [/b][quote]Correct answer: select top 1000 col1 from bigtable, select top 10 percent col1 from bigtable [/quote]I too selected a single answer not multiple answers[b]KevRiley - please accept my sincere apologies for my stupidity [/b]KevRiley the question as copied and pasted is:[quote]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 [b]guaranteed [/b]to return 1000 rows [/quote]Emphasis on [b]quaranteed [/b]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 [quote]The quotes you provide, are exactly what I am saying with the question - TABLESAMPLE will return [b]approximately [/b]the specified number of rows - not a guaranteed number.[/quote] Seems like the question has been re-worded but enough of this quibbling ...Wed, 30 Jul 2008 09:04:14 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxbitbucket - 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?Wed, 30 Jul 2008 08:27:37 GMTkevrileyhttp://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspx[b]DISREGARD THIS RANTING [/b][quote]Correct answer: select top 1000 col1 from bigtable, select top 10 percent col1 from bigtable [/quote]I too selected a single answer not multiple answers[b]KevRiley - please accept my sincere apologies for my stupidity [/b]Sorry but I can NOT agree with the supposed correct answerFrom SQL Server 2005 BOL (September 2007)ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8868e8fd-6c42-4171-9eab-a0e38cb1bfd3.htm[quote]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 [b]approximately [/b]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 ;GOSELECT FirstName, LastNameFROM Person.Contact TABLESAMPLE (100 ROWS) ; [/quote]Further on the same BOL page[quote]The Person.Contact table contains 19,972 rows. The following example returns [b]approximately[/b] 10 percent of the rows. The number of rows returned usually changes every time that the statement is executed. Copy Code USE AdventureWorks ;GOSELECT FirstName, LastNameFROM Person.Contact TABLESAMPLE (10 PERCENT) ; [/quote]Emphasis on [b]approximately[/b] 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 [b]approximately[/b] the number specified.Wed, 30 Jul 2008 08:20:14 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxCorrect answer: select top 1000 col1 from bigtable, select top 10 percent col1 from bigtable It doesn't say to choose all correct statements!Wed, 30 Jul 2008 08:16:04 GMTjon.harthttp://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxOther 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 beset rowcount 0 go select top 1000 col1 from mytableYes? 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.Wed, 30 Jul 2008 07:19:29 GMTJamie Longstreet-481950http://www.sqlservercentral.com/Forums/Topic543173-1228-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/SQL+Server+2005+-+TSQL/63264/">TOP and TABLESAMPLE</A>[/B]Tue, 29 Jul 2008 21:51:30 GMTkevriley