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