COUNT_BIG

Question

Post reply

COUNT_BIG

agrawal.prakriti

Mr or Mrs. 500

Points: 572
More actions
March 8, 2010 at 8:10 pm

#386463

Comments posted to this topic are about the item COUNT_BIG

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

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

Fal Hall of Fame Points: 3143 More actions · Answer 1

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

Thanks for the questions Prakriti.

S.

Open Minded SSCommitted Points: 1872 More actions · Answer 2

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

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
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 4

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 SSCommitted Points: 1872 More actions · Answer 5

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 SSCrazy Points: 2260 More actions · Answer 6

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: 1171 More actions · Answer 7

p.ramchander

Ten Centuries

Points: 1171

March 9, 2010 at 1:43 am

#1130498

Thanks for the question. Learn something new today.

simon.whale Ten Centuries Points: 1393 More actions · Answer 8

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 SSCrazy Points: 2522 More actions · Answer 9

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 More actions · Answer 10

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

antuan.steyn SSC Enthusiast Points: 102 More actions · Answer 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... 🙂

Tom Garth SSCertifiable Points: 6193 More actions · Answer 12

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 SSChampion Points: 11139 More actions · Answer 13

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

DBA_Dom SSCrazy Points: 2902 More actions · Answer 14

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.