TABLESAMPLE output

  • Anoo S Pillai

    Mr or Mrs. 500

    Points: 547

    Comments posted to this topic are about the item TABLESAMPLE output

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    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: 258985

    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: 258985

    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

    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: 258985

    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

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

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    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

    Thank you for the replies

  • free_mascot

    One Orange Chip

    Points: 27168

    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."

  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286985

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

  • twin.devil

    SSC-Insane

    Points: 22208

    Nice question Anoo. learn something new today. thanks

  • palotaiarpad

    SSCertifiable

    Points: 5615

    Nice question!

    But i can't get the purpose of this.

    How can an unpredictable option be useful?:-D

  • david.gugg

    SSCertifiable

    Points: 5696

    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...


    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]

    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

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

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