InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 23:39:06 GMT20http://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxexplanation given here is not fully correct-->"SELECT COUNT_BIG(ALL COlumn_2) and SELECT COUNT_BIG(column_2). COUNT_BIG(all column_2) will count all rows from the table (including duplicate & null rows). COUNT_BIG(DISTINCT column_2) will evaluate distinct non-null values. :discuss:because ---> "COUNT_BIG(all column_2) will count all rows from the table including duplicate & Excluding null rows).andCOUNT_BIG(*) will count all rows from the table (including duplicate & null rows). Thu, 30 Aug 2012 01:29:31 GMTshemu1990http://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxexplination given here is not fully correct-->"SELECT COUNT_BIG(ALL COlumn_2) and SELECT COUNT_BIG(column_2). COUNT_BIG(all column_2) will count all rows from the table (including duplicate & null rows). COUNT_BIG(DISTINCT column_2) will evaluate distinct non-null values. :discuss:because ---> "COUNT_BIG(all column_2) will count all rows from the table including duplicate & Excluding null rows).andCOUNT_BIG(*) will count all rows from the table (including duplicate & null rows). Thu, 30 Aug 2012 01:24:33 GMTshemu1990http://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxI think this question could have been worded better and is incomplete.Fri, 29 Apr 2011 07:52:16 GMTterrykzncshttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxAll is by default so II & III are same expressionsMon, 27 Sep 2010 17:22:39 GMTDhruvesh Shahhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspx[quote][b]Avaneesh -388582 (5/20/2010)[/b][hr]Hi,I. SELECT COUNT_BIG(*)II. SELECT COUNT_BIG(column_2)III. SELECT COUNT_BIG(ALL column_2)IV. SELECT COUNT_BIG(DISTINCT column_2)I Answered this question selecting II,III and IV (which actually is a correct answer) guess what i was indicated as INCORRECT! Pls let me know if I am so.coz all the three (2,3,4) have same output.[/quote]Wow! That's amazing! What code did you use to come to that conclusion? I used this:[code="sql"]DECLARE @QotDTABLE ( Column_1 TINYINT NOT NULL, Column_2 TINYINT NULL ); INSERT @QotD VALUES (1, 1);INSERT @QotD VALUES (2, NULL);INSERT @QotD VALUES (3, 2);INSERT @QotD VALUES (4, 2);INSERT @QotD VALUES (5, NULL);INSERT @QotD VALUES (6, 3);INSERT @QotD VALUES (7, 4);INSERT @QotD VALUES (8, 5);INSERT @QotD VALUES (9, 6);INSERT @QotD VALUES (10, 7);--I.SELECT COUNT_BIG(*) FROM @QotD;--II.SELECT COUNT_BIG(Column_2) FROM @QotD;--III.SELECT COUNT_BIG(ALL Column_2) FROM @QotD;--IV.SELECT COUNT_BIG(DISTINCT Column_2) FROM @QotD;[/code]I Answered this question selecting II,III (which actually is a correct answer) guess what i was indicated as CORRECT! Pls let me know if I am so. coz all the two (2,3) have same output. :laugh:Thu, 20 May 2010 03:09:22 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxHi,I. SELECT COUNT_BIG(*)II. SELECT COUNT_BIG(column_2)III. SELECT COUNT_BIG(ALL column_2)IV. SELECT COUNT_BIG(DISTINCT column_2)I Answered this question selecting II,III and IV (which actually is a correct answer) guess what i was indicated as INCORRECT! Pls let me know if I am so.coz all the three (2,3,4) have same output.Thu, 20 May 2010 00:52:14 GMTAvaneesh -388582http://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxThe ans given here is wrong;i used here declare @temp table (num int, numdesc varchar(50))insert @temp select 1, 'one' union select 2, 'two' union select 3, null union select 4, 'four'union select 5, nulli) select count_big(*) from @tempii) select count_big(num) from @tempiii) select count_big(numdesc) from @tempiv) select count_big(all numdesc) from @tempv) select count_big(distinct numdesc) from @tempand the same result were froma) i) and ii)b) iii), iv) and v)Thu, 15 Apr 2010 00:13:16 GMTcolyanhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxGood question, thank you.Tue, 30 Mar 2010 08:14:26 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxInteresting question... adds to the sql vocabulary...Sun, 21 Mar 2010 08:02:07 GMTJamie Longstreet-481950http://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspx[quote][b]r5d4 (3/9/2010)[/b][hr]the answer is I and II , not II and III.does anyone audit these questions? i want my point (joke) :)[/quote]That was my answer too but it's wrong. I forgot that ALL is the default value for first argument, not *. It can't be I and II, because (*) will count null values, none of the other options will.Cheers!Thu, 18 Mar 2010 17:50:30 GMTjim.tinneyhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxI used deductive reasoning more than SQL skills to guess this right :-DI assumed Number 1 would return both columns, the other 3 would return only column 2 and number 4 would include one NULL (doesn't DISTINCT include one NULL?) so I assumed that ALL was the default and would therefore make 2 and 3 have identical results. But I guessed :-DIs my explanation close to being right?Mon, 15 Mar 2010 12:51:06 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxThis was a very very good question. Event I read the remark section in SQL Server 2008 Books Online about Count_big(COUNT_BIG(*) returns the number of items in a group. This includes NULL values and duplicates.COUNT_BIG(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.COUNT_BIG(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.), I got it wrong and did not understand why my answer was wrong until I had read all the 4 pages of reply...Thanks you all!Fri, 12 Mar 2010 18:39:20 GMTtilew-948340http://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspx[quote][b]ravindraee24 (3/10/2010)[/b][hr]why SELECT COUNT_BIG(*) not return same result????????????i hope:cool: 1,2,and 3 are correct[/quote]There is an explanation by sknox on page 2 of this discussion, which clearly explains why 2 and 3 are the only correct options. Please read the post by sknox. There are more related posts after that, on page 3.OlegThu, 11 Mar 2010 08:49:24 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxwhy SELECT COUNT_BIG(*) not return same result????????????i hope:cool: 1,2,and 3 are correctWed, 10 Mar 2010 23:08:42 GMTkumar ravindrahttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxOleg, thank you very much for the time and explanation, you couldn't have been clearer. i'm gonna play with your code at lunch time :p .I've learned a lot more than the count_big function out of this. TYVM again!Greetings!Wed, 10 Mar 2010 05:54:37 GMTsistemas 95572http://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxBasically, page 3 blew me away :-DTue, 09 Mar 2010 18:16:00 GMTOpen Mindedhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspx[quote][b]sistemas 95572 (3/9/2010)[/b][hr]Thank you Oleg, I am curious about uniquely identifying rows in tables w/o PKs (sorry about the off topic).I mean, as I understand from your explanation there is some "row id" that we have no access to, and it´s managed by the dbms.If that is the case, let's assume a table with 2 int cols, with no primary key, and then you store 5 times the same values, ie:create table t2 (a int, b int)insert into t2 values (1,1)insert into t2 values (1,1)insert into t2 values (1,1)insert into t2 values (1,1)How can you actually uniquely identify one of those rows let's say if i want to delete [b]only one[/b] of those?I've tryed doing it with enterprise manager, but got an error such as "wrong primary key or insufficcient column information".Sorry about the weird case, i'm a student just taking DB course :p[/quote]Actually, you have 4 records in t2, but lets say there are 5 of them. Suppose you want to delete only one of those records while leaving other 4 intact. Since as lesser mortals, we do not have access to rowid, we cannot just issue the statement to, for example, delete THE_THIRD_ROW. On the other hand, if all we care is to delete one of the existing rows sparing other 4 then we could not care less which one we actually delete, because they all look the same to us. Therefore, we can do something like this (assuming that you have SQL Server 2005 or better):[code="sql"]with cte (RecordNumber, a, b) as( select row_number() over (order by a) RecordNumber, * from t2) delete from cte where RecordNumber = 3;[/code]This will delete the row which we perceive as number 3, still giving us the result we need (One row deleted, other 4 are spared).Usually, the problems like this (delete the dups) arise when there is a need to get rid of all the duplicates in the existing heap table. Lets expand the question like this: I will add 3 more records into t2, 2 records with a = 2 and 1 record with a = 3. [code="sql"]insert into t2 values (2,1);insert into t2 values (2,1);insert into t2 values (3,1);[/code]This will yield t2 to have 5 records with a = 1, 2 records with a = 2 and 1 record with a = 3. Out of those, the last record is already clean but all records before that have dups. Suppose we want to get rid of all dups while preserving all unique rows. In other words, the end result is expected to have t2 with one record with a = 1, 1 record with a = 2, and 1 record with a = 3. The statement to do this can be like this:[code="sql"]with cte (PartitionedNumber, a, b) as( select row_number() over (partition by a order by a) PartitionedNumber, * from t2) delete from cte where PartitionedNumber > 1;[/code]Now selecting * from t2 will return just 3 leftover unique records.OlegTue, 09 Mar 2010 13:32:20 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxThank you Oleg, I am curious about uniquely identifying rows in tables w/o PKs (sorry about the off topic).I mean, as I understand from your explanation there is some "row id" that we have no access to, and it´s managed by the dbms.If that is the case, let's assume a table with 2 int cols, with no primary key, and then you store 5 times the same values, ie:create table t2 (a int, b int)insert into t2 values (1,1)insert into t2 values (1,1)insert into t2 values (1,1)insert into t2 values (1,1)How can you actually uniquely identify one of those rows let's say if i want to delete [b]only one[/b] of those?I've tryed doing it with enterprise manager, but got an error such as "wrong primary key or insufficcient column information".Sorry about the weird case, i'm a student just taking DB course :pTue, 09 Mar 2010 13:11:30 GMTsistemas 95572http://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspx[quote][b]sistemas 95572 (3/9/2010)[/b][hr]Hey pal,what do you mean by volatile rowid from the heap?Check a couple of posts upstairs, i pointed out the case of a full nulled row, but wasn't sure "why" count(*) included that kind of rows.Could it be just implementation desicion?Could the count(*) function get its return value from some sort of catalog when there is no where clause, instead of actually getting into the data?[/quote]What I meant is that even though you have a table with rows which have null in every column, every row in the table can still be uniquely identified by the engine, which actually stores the data on pages. If you have rows with null in every column then the odds are that [i][b]this is a heap table[/b][/i], and from what I understand, the rowid is the only way for the engine to uniquely identify every record in such table.I cannot speculate whether implementation of the count(*) gets, as you say, [b]"its return value from some sort of catalog"[/b], I just suggested that it might use the rowid (those are always unique and never null) to figure the count. In other words, I suggested that yes, it is [b]"actually getting into the data"[/b].Sorry that I missed your earlier post, I apologize.OlegTue, 09 Mar 2010 12:53:24 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxHey pal,what do you mean by volatile rowid from the heap?Check a couple of posts upstairs, i pointed out the case of a full nulled row, but wasn't sure "why" count(*) included that kind of rows.Could it be just implementation desicion?Could the count(*) function get its return value from some sort of catalog when there is no where clause, instead of actually getting into the data?Tue, 09 Mar 2010 12:31:49 GMTsistemas 95572http://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxI, also, learned something new. Thanks.Tue, 09 Mar 2010 11:35:50 GMTKevinC.http://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspx[quote][b]sjimmo (3/9/2010)[/b][hr]Oleg,Thanks for the explanatin. You're right - I did not have any nulls.[/quote]Looks like I am getting the credit I don't deserve :w00t:. The original explanation was posted by[b] sknox[/b], I just used it as a quoted reply and added the snippet to it.OlegTue, 09 Mar 2010 10:49:39 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspx[quote][b]CirquedeSQLeil (3/9/2010)[/b][hr]Nice explanation and test setup Oleg.[/quote]Thank you Jason. I wish the day will come when somebody will explain to me how to properly format the results output when including it in the post. Normal copy from SSMS results pane initially looks good but then all tabs and extra spaces get replaced by a single space (default html behavior). I tried to use the [code] tag, but it still does not look right :hehe: The cave man attempt to cheat by typing [pre]the results here[/pre] does not work either because the pre tag does not get parsed.OlegTue, 09 Mar 2010 10:45:29 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxOleg,Thanks for the explanatin. You're right - I did not have any nulls.Tue, 09 Mar 2010 10:40:51 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxNice explanation and test setup Oleg.Tue, 09 Mar 2010 10:24:20 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspx[quote][b]sknox (3/9/2010)[/b][hr][quote][b]sjimmo (3/9/2010)[/b][hr]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_table98481SELECT COUNT_BIG(testfield2) FROM test_table98481SELECT COUNT_BIG(ALL testfield2) FROM test_table98481SELECT COUNT_BIG(DISTINCT testfield2) FROM test_table28452Based 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?[/quote]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 testfield2So, (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.[/quote]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:[code="sql"]declare @boloney table (column_1 int, column_2 int);insert into @boloneyselect 1, 1unionselect 2, nullunionselect 3, 2unionselect 4, 2unionselect 5, 3unionselect 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[/code]The result of the above will be [code="plain"]opt_1 opt_2 opt_3 opt_4----- ----- ----- ----- 6 4 4 3[/code]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.OlegTue, 09 Mar 2010 10:21:12 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxI 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 non-null 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.Tue, 09 Mar 2010 10:11:31 GMTkevin.l.williamshttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspx[quote][b]r5d4 (3/9/2010)[/b][hr]the answer is I and II , not II and III.does anyone audit these questions? i want my point (joke) :)[/quote]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)Tue, 09 Mar 2010 08:30:13 GMTerwin.oosterhoornhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspx[quote][b]sjimmo (3/9/2010)[/b][hr]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_table98481SELECT COUNT_BIG(testfield2) FROM test_table98481SELECT COUNT_BIG(ALL testfield2) FROM test_table98481SELECT COUNT_BIG(DISTINCT testfield2) FROM test_table28452Based 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?[/quote]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 testfield2So, (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.Tue, 09 Mar 2010 07:35:00 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxI was confused enough by the explanation that I had to try this myself. Two statements in the explanation are contradictory (emphasis mine):[quote]... COUNT_BIG(all column_2) will count all rows from the table ([b][u]including[/u][/b] duplicate & [b][u]null rows[/u][/b]). COUNT_BIG(DISTINCT column_2) will evaluate distinct non-null 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 [b][u]returns the number of nonnull values.[/u][/b] ...[/quote]II and III are correct, by the way.Tue, 09 Mar 2010 07:31:23 GMTEd Leighton-Dickhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxcount_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, nullselect * from @tselect count_big(*) from @tselect count_big(x) from @tcount_big(*) returns 4, and count_big(x) returns 2.Tue, 09 Mar 2010 07:10:41 GMTsistemas 95572http://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxExplaination is a little off. [quote]COUNT_BIG(all column_2) will count all rows from the table (including duplicate & null rows). [/quote]I think you meant count_big(*). COUNT_BIG(all column_2) will not count the NULLs.Tue, 09 Mar 2010 06:57:06 GMTDBA_Domhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxGood 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_table98481SELECT COUNT_BIG(testfield2) FROM test_table98481SELECT COUNT_BIG(ALL testfield2) FROM test_table98481SELECT COUNT_BIG(DISTINCT testfield2) FROM test_table28452Based 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?Tue, 09 Mar 2010 06:52:47 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxI too got it wrong because I needed a refresher regarding the evil side of NULL.Good QotD - ThanksTue, 09 Mar 2010 06:47:02 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxI 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... :-)Tue, 09 Mar 2010 04:25:24 GMTantuan.steynhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxi think you are incorrect as from this simple example script (im using sql2005)declare @t table(i int, x int)insert into @tselect 1, nullunionselect 3, 1union select 2, 1select *from @tselect count_big(*)from @tselect count_big(x)from @tit shows that count_big ignored the nulls in the second queryTue, 09 Mar 2010 03:22:55 GMTsimon.whalehttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxthe answer is I and II , not II and III.does anyone audit these questions? i want my point (joke) :)Tue, 09 Mar 2010 03:13:49 GMTr5d4http://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxGreat question, made you think about the simple functions that we take for granted, even though i have never needed to use count_bigTue, 09 Mar 2010 02:04:50 GMTsimon.whalehttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxThanks for the question. Learn something new today.Tue, 09 Mar 2010 01:43:44 GMTp.ramchanderhttp://www.sqlservercentral.com/Forums/Topic879054-2605-1.aspxThanks 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.Tue, 09 Mar 2010 01:35:44 GMTjshailendra