• sknox (3/9/2010)


    sjimmo (3/9/2010)


    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

    98481

    SELECT COUNT_BIG(testfield2) FROM test_table

    98481

    SELECT COUNT_BIG(ALL testfield2) FROM test_table

    98481

    SELECT COUNT_BIG(DISTINCT testfield2) FROM test_table

    28452

    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?

    No. It appears that you have no NULL values in testfield2. Here's the breakdown:

    (a) COUNT_BIG(*) is essentially a row count function -- it returns the number of rows returned, including those with NULL values in any column.

    (b) COUNT_BIG(testfield2) will return the number of rows where testfield2 is NOT NULL.

    (c) COUNT_BIG(ALL testfield2) is the same as the previous, as ALL is the default.

    (d) COUNT_BIG(DISTINCT testfield2) will return the number of unique nonnull values of testfield2

    So, (b) and (c) are always equal. If (a) is equal to (b) and (c), that means you have no NULLs in testfield2 in your result set. If (d) is equal to (b) and (c), that means you have no duplicate values in testfield2 in your result set. If they're all 4 equal, that means testfield2 is a column with all unique non-null values.

    This is an excellent explanation!. The (a) holds true even if you have a table with records having null in every column (not that you ever want to have such table :-)).

    Consider, for example:

    declare @boloney table (column_1 int, column_2 int);

    insert into @boloney

    select 1, 1

    union

    select 2, null

    union

    select 3, 2

    union

    select 4, 2

    union

    select 5, 3

    union

    select null, null;

    select

    count_big(*) option_1,

    count_big(column_2) option_2,

    count_big(all column_2) option_3,

    count_big(distinct column_2) option_4

    from @boloney;

    go

    The result of the above will be

    opt_1 opt_2 opt_3 opt_4

    ----- ----- ----- -----

    6 4 4 3

    Even though there is a row with null in every column, the count_big(*) just like count(*) will still include it because it returns the number of rows in the table regardless of any values in any columns. This is probably because it includes the volatile rowid of the heap table below and the rowid is never null.

    Oleg