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


TOP and TABLESAMPLE


TOP and TABLESAMPLE

Author
Message
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3152 Visits: 838
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
bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8091 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


I have edited my previous 2 posts to reflect the above.

Again - thanks for not kicking my butt too hard, but I deserved it

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
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3671 Visits: 3889
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
Miles Neale
Miles Neale
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: 3102 Visits: 1694
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.

ThanksSmile

Miles...

Not all gray hairs are Dinosaurs!
kevriley
kevriley
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3174 Visits: 2618
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 BigGrin


Kev
Steve Jones
Steve Jones
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: Administrators
Points: 66202 Visits: 19120
OK, 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 notw00t

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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: Administrators
Points: 66202 Visits: 19120
As 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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Melville
Melville
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 18
I'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:

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.


This is silly.



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91200 Visits: 41151
Wow... 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! Tongue

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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