

SSC Eights!
Group: General Forum Members
Last Login: Friday, November 7, 2014 8:25 AM
Points: 934,
Visits: 179


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 union select 3, 1 union 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




SSC Rookie
Group: General Forum Members
Last Login: Friday, July 6, 2012 1:19 AM
Points: 44,
Visits: 11


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




SSC Eights!
Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977,
Visits: 1,499


I too got it wrong because I needed a refresher regarding the evil side of NULL.
Good QotD  Thanks
Tom Garth Vertical Solutions
"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




SSCrazy
Group: General Forum Members
Last Login: Friday, January 9, 2015 11:32 AM
Points: 2,917,
Visits: 2,538


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




Mr or Mrs. 500
Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 568,
Visits: 78,299


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.




SSC Rookie
Group: General Forum Members
Last Login: Tuesday, February 25, 2014 1:16 PM
Points: 29,
Visits: 32


count_big(*) will count every row in table, including rows with null values on every column, probably because it gets the amount of rows out of catalog?
Try this:
declare @t table(i int, x int)
insert into @t select 1, null union select 3, 1 union select 2, 1 union select null, null
select * from @t
select count_big(*) from @t
select count_big(x) from @t
count_big(*) returns 4, and count_big(x) returns 2.




Ten Centuries
Group: General Forum Members
Last Login: Monday, November 24, 2014 9:39 AM
Points: 1,328,
Visits: 428


I was confused enough by the explanation that I had to try this myself. Two statements in the explanation are contradictory (emphasis mine):
... COUNT_BIG(all column_2) will count all rows from the table (including duplicate & null rows). COUNT_BIG(DISTINCT column_2) will evaluate distinct nonnull values. 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. ...
II and III are correct, by the way.
Ed LeightonDick MCITP, MCTS




Ten Centuries
Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 1,414,
Visits: 1,801


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




Say Hey Kid
Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 708,
Visits: 282


r5d4 (3/9/2010) the answer is I and II , not II and III.
does anyone audit these questions? i want my point (joke) :) Me too, me too... It seems that there are more than 2 with the right answer if II and III are correct, as I is then correct too?? Also, the question is a bit vague:From following data, which two queries will return identical results? (select all that apply) Do I select only 2 answers or all that apply?
anyone, enlighten me as why the first answer is not the same as the 3rd answer? (just seen the answer in the reply above this, OK i don't get the points)




Hall of Fame
Group: General Forum Members
Last Login: Wednesday, January 21, 2015 11:30 AM
Points: 3,198,
Visits: 1,273


I believe that this is what the explanation should have been.
The answers are: SELECT COUNT_BIG(ALL COlumn_2) 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 nonnull values.
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.
COUNT_BIG is works like COUNT, only difference is that COUNT_BIG returns bigint data type.



