The TOP 10 Count

  • Eirikur Eiriksson

    SSC Guru

    Points: 182347

    Comments posted to this topic are about the item The TOP 10 Count

  • HappyGeek

    SSCoach

    Points: 18657

    Good question, had to pause for thought and do a bit of math!

    ...

  • twin.devil

    SSC-Insane

    Points: 22208

    Good question. Thanks for sharing.

  • This was removed by the editor as SPAM

  • martin.whitton

    SSCrazy

    Points: 2568

    Of course, if you have more than 2^21 columns in your database, it will return an error ... 😀

  • michal.lisinski

    SSCertifiable

    Points: 6933

    My first thought - 1 row and why the hell there's no such answer, and then

    slow down, review carefully all available. 😎

    Thanks Eirikur

  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286957

    Very nice question Eirikur. It makes you stop and think...just for a moment.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Was looking for the trick.

  • Rich Mechaber

    SSChampion

    Points: 10935

    Got it right, but only because I read the question too quickly and was looking for "1" as a possible answer, b/c I thought the question read "How many rows are returned?" When "1" wasn't in the list of possible answers, I figured I needed to actually read the question you wrote (vs. the one I thought you wrote)! 😀

    If anyone was thinking "1000" for an answer, this will do that, b/c it first returns 10 from the CTE using TOP:

    ;WITH cte As

    (

    SELECT TOP (10) *

    FROM sys.all_columns

    )

    SELECT COUNT_BIG(*)

    FROM

    cte cte1 CROSS JOIN

    cte cte2 CROSS JOIN

    cte cte3;

    Rich

  • George Vobr

    SSCrazy Eights

    Points: 8951

    Very useful question Eirikur, thank you. 🙂 There is also interesting that COUNT_BIG(*) is here a deterministic function,

    because the TOP is not specified with an aggregate clause.

    See eg.: https://msdn.microsoft.com/en-us/library/ms190317.aspx"> https://msdn.microsoft.com/en-us/library/ms190317.aspx

  • DonlSimpson

    SSCertifiable

    Points: 6774

    Stewart "Arturius" Campbell (10/22/2015)


    martin.whitton (10/22/2015)


    Of course, if you have more than 2^21 columns in your database, it will return an error ... 😀

    if one has that many columns, they will have even more serious problems....:hehe:

    +1

    Don Simpson



    I'm not sure about Heisenberg.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182347

    Stewart "Arturius" Campbell (10/22/2015)


    martin.whitton (10/22/2015)


    Of course, if you have more than 2^21 columns in your database, it will return an error ... 😀

    if one has that many columns, they will have even more serious problems....:hehe:

    It would take 67492 tables with 30000 sparse columns each to reach the limit of a bigint (2^63) - 1;-)

    😎

  • Michael Poppers

    SSCrazy

    Points: 2119

    Two points to Michal L., and take away two from me for not considering which choice actually represented the "1" answer I was seeking :).

  • HappyGeek

    SSCoach

    Points: 18657

    As a thought you could have explained why use Count_Big rather than just Count and included a link.

    But there again you could have used Count, and included another option; an error is returned, Possibly?

    ...

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

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