TABLESAMPLE output

Question

TABLESAMPLE output

Anoo S Pillai

Mr or Mrs. 500

Points: 547
More actions
April 2, 2014 at 8:06 pm

#291906

Comments posted to this topic are about the item TABLESAMPLE output
April 3, 2014 at 3:13 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 1

For the given example it returns exactly 5 rows, tablesample option might give different output if the table has more no of rows. :unsure:

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 2

Very interesting question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 3

pmadhavapeddi22 (4/2/2014)
For the given example it returns exactly 5 rows, tablesample option might give different output if the table has more no of rows. :unsure:

Yes indeed, that's the whole point of the question.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 4

when we are specifying high range of rows nearing to the exact no of rows then it will return all the rows of the table

like , in the QOD

we have total no of rows 5 and tablesample is given for 4 rows, then that particular statement will return 5 rows.

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 5

pmadhavapeddi22 (4/3/2014)
when we are specifying high range of rows nearing to the exact no of rows then it will return all the rows of the table
like , in the QOD
we have total no of rows 5 and tablesample is given for 4 rows, then that particular statement will return 5 rows.

That's because you probably specify a number of rows that is located on the total amount of pages used for that table.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 6

My point is, for the given question it returns 5 rows, so answer is 5 🙂

Koen Verbeeck SSC Guru Points: 259155 More actions · Answer 7

pmadhavapeddi22 (4/3/2014)
My point is, for the given question it returns 5 rows, so answer is 5 🙂

Ah ok, I didn't get your point 😀

Execute the query a few times, it will return 0 at some point.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 8

pmadhavapeddi22

SSCertifiable

Points: 5306

April 3, 2014 at 12:54 am

#1702742

Thank you for the replies

free_mascot One Orange Chip Points: 27164 More actions · Answer 9

Very interesting question, Thanks!

Also one can try restarting sql server (test only) and execute the query it will give you 0 record and later 5 rows.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

I'd never used this option either. Very interesting, so thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Umer Akram SSC-Insane Points: 22209 More actions · Answer 11

Nice question Anoo. learn something new today. thanks

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 12

Nice question!

But i can't get the purpose of this.

How can an unpredictable option be useful?:-D

david.gugg SSCertifiable Points: 6135 More actions · Answer 13

Great question, I'd never heard of TABLESAMPLE, so I looked it up - and then still ended up getting the answer wrong :O

Now I'm just trying to think of when I could use this in my day to day duties...

Be still, and know that I am God - Psalm 46:10