• agrawal.prakriti

    Mr or Mrs. 500

    Points: 572

    Comments posted to this topic are about the item COUNT_BIG

  • Fal


    Points: 2984

    Gosh, I'm the FIRST person to answer this QotD!

    Thanks for the questions Prakriti.


  • Open Minded


    Points: 1842

    I read up what is the definition of count_big before I answered half-blindly. I never used it before and I may use it only years from now, since our transactional data is still too far from 2 billion rows (int, 4 bytes).

    But thanks anyway.

    *edited - our table with highest number of rows is still too far from even 10 million.


    SSC Guru

    Points: 281252

    Maybe this is a bit nitpicky, but the answer explanation doesn't seem to match the answer.

    The answers are: SELECT COUNT_BIG(*) and SELECT COUNT_BIG(column_2).

    This sentence does not match the correct answers. It seems somewhat confusing and could create a bit of a stir.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Jan Van der Eecken

    SSCrazy Eights

    Points: 9004

    I too had to look up COUNT_BIG in BOL since i wasn't even aware it existed. So thanks for the question, I learned something new yet again. Still got the answer wrong though. I realized a split second after clicking the Answer button that the point was gone 🙁

    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Open Minded


    Points: 1842

    CirquedeSQLeil (3/9/2010)

    The answers are: SELECT COUNT_BIG(*) and SELECT COUNT_BIG(column_2).

    This sentence does not match the correct answers. It seems somewhat confusing and could create a bit of a stir.

    This is wiered...I answered II and III, thereby getting both correct?

    SELECT COUNT_BIG(column_2)

    SELECT COUNT_BIG(ALL column_2)

    ALL is the default and hence COUNT_BIG(column 2) is same as COUNT_BIG(ALL column_2). This evaluates expression for each row in a group and returns the number of nonnull values.

    Maybe the first paragraph of the explanation was wrong?

    The answers are: SELECT COUNT_BIG(*) and SELECT COUNT_BIG(column_2). COUNT_BIG(*) will count all rows from the table (including duplicate & null rows). COUNT_BIG(DISTINCT column_2) will evaluate distinct non-null values.

    If the correct answers are different from mine, it's alright for me to have my points deducted. I know I will never be able to verify the answers without wrecking my server's disk space or processing power.

  • jshailendra


    Points: 2260

    Thanks for the question..hurray I got it correct...but only after reading explanation about it in BOL...I never heard about it before...but its a very useful function...

    Yes..the explanation given by OP was wrong..answer II and III is absolutely correct.

  • p.ramchander

    Ten Centuries

    Points: 1111

    Thanks for the question. Learn something new today.

  • simon.whale

    Ten Centuries

    Points: 1393

    Great question, made you think about the simple functions that we take for granted, even though i have never needed to use count_big

  • r5d4


    Points: 2499

    the answer is I and II , not II and III.

    does anyone audit these questions? i want my point (joke) 🙂

  • simon.whale

    Ten Centuries

    Points: 1393

    i think you are incorrect as from this simple example script (im using sql2005)

    declare @t table(i int, x int)

    insert into @t

    select 1, null


    select 3, 1


    select 2, 1

    select *

    from @t

    select count_big(*)

    from @t

    select count_big(x)

    from @t

    it shows that count_big ignored the nulls in the second query

  • antuan.steyn

    SSC Enthusiast

    Points: 102

    I answered I and II and according to the explanation, it should be right. I need my point, I'm only on Grasshopper status! Need points... 🙂

  • Tom Garth


    Points: 6173

    I too got it wrong because I needed a refresher regarding the evil side of NULL.

    Good QotD - Thanks

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • sjimmo


    Points: 11139

    Good question - Thought that I knew the answer, and was wrong. Now, I have a question. I ran the following against an existing database to prove/disprove the answer, and found the following:

    SELECT COUNT_BIG(*) FROM test_table


    SELECT COUNT_BIG(testfield2) FROM test_table


    SELECT COUNT_BIG(ALL testfield2) FROM test_table


    SELECT COUNT_BIG(DISTINCT testfield2) FROM test_table


    Based upon testing, though brief, and the answer, I would assume that the correct answer is 1, 2, and 3. The answer description states that ALL is the default, whether used or not thus selection 1 would use the default of ALL as shown in the above example, correct?

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • DBA_Dom


    Points: 2842

    Explaination is a little off.

    COUNT_BIG(all column_2) will count all rows from the table (including duplicate & null rows).

    I think you meant count_big(*). COUNT_BIG(all column_2) will not count the NULLs.

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

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