What's the differences among Count(0), Count(1) and Count(SomeField)

Hi all

Several questions want to know

What's select count(0) in MSSQL

What's select count(1) in MSSQL

What's select count(FieldName) in MSSQL

How about their speedAnd if FieldName is a primary keywhy the speed is the slowestI don't know why

Reguards

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

Well,

Count(1) will run faster than Count(*).

Also Count(1) will return the number of rows and not the data.

count(FieldName) will only count table where fiedName is not null.

This might help you http://www.techonthenet.com/sql/count.php

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

Count(*)/Count(0)/Count(1) - All are same and it gives the number of rows in the result set. But if you specify the column name, it gives the number of rows in the result set where the specified column is not null.

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

Hey all again

Why count(0) and count(1)What does 0 and 1 mean hereAnd what's the MOST DIFFERENCE BETWEEN the two statements

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

TimoYang

Hey all again

Why count(0) and count(1)What does 0 and 1 mean hereAnd what's the MOST DIFFERENCE BETWEEN the two statements

There is no difference.

you must see this

select count(0)  --0 means expression only
select count(1)  --1 means expression only

---both above results show output one
--becuase count function require

--as well as check this
select COUNT(*)


--this will also return value 1 becuase count function is not used in select query

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

TimoYang
Why count(0) and count(1)What does 0 and 1 mean hereAnd what's the MOST DIFFERENCE BETWEEN the two statements

I would suggest you to check out below links, which will clear you doubts :

http://blog.sqlauthority.com/2008/02/26/sql-server-select-1-vs-select-an-interesting-observation/

http://beyondrelational.com/modules/2/blogs/116/posts/12000/sql-server-count-or-count1-which-is-better.aspx

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

Hi, I believe count(0) is counting the first column, Count(1) would be the second column in the table.

Since your specifying the column here, it will only count the non-null records in the specified column. Count(*) will give you a true count of the rows in the table.

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

christiandev
Hi, I believe count(0) is counting the first column, Count(1) would be the second column in the table.

No. COUNT(0) & COUNT(1) does not specify column name at all.

0,1 & any thing inside are only expression say value..

SELECT COUNT(0) --return 1

SELECT COUNT(1) --return 1

SELECT COUNT(100) --return 1

SELECT COUNT(*) --return 1

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

yrb.yogi

No. COUNT(0) & COUNT(1) does not specify column name at all.

0,1 & any thing inside are only expression say value..

Very True.

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

yrb.yogi

christiandev
Hi, I believe count(0) is counting the first column, Count(1) would be the second column in the table.

No. COUNT(0) & COUNT(1) does not specify column name at all.

0,1 & any thing inside are only expression say value..

SELECT COUNT(0) --return 1

SELECT COUNT(1) --return 1

SELECT COUNT(100) --return 1

SELECT COUNT(*) --return 1

I always use Count(*), and I was under the impression that the value was the ordinal position - learn something every day :) - the rest is still correct though, count(*) will return even when null, count(column_name) will only return non-null in the count?

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

christiandev
Hi, I believe count(0) is counting the first column, Count(1) would be the second column in the table.

If soWhy can I also do select count(1000)My table only has two columnsIt should be wrong

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

yrb.yogi
There is no difference.

WhyDon't you think Microsoft is so "stupid" to develop such a duplicated functionhahahaI'm afraid it's not the reason

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

How about their speed between select count(0) select count(1) and select count(FieldName)Why

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

HarryBrock
Count(1) will run faster than Count(*).

I was once told by someone that Count(*) is faster than Count(1)

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

TimoYang
I was once told by someone that Count(*) is faster than Count(1)

Did you look at the link from my first post?

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

HarryBrock
Did you look at the link from my first post?

I lookedhowever I think now that count(1) will be automatically converted to count(*)and this will take sometime

http://stackoverflow.com/questions/1221559/count-vs-count1

NOT SURE WHO IS RIGHT

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

TimoYang

I lookedhowever I think now that count(1) will be automatically converted to count(*)and this will take sometime

http://stackoverflow.com/questions/1221559/count-vs-count1

NOT SURE WHO IS RIGHT

True. If you are using the count function with select query with tablename.

False. if you using with only select without specifying the tablename.

--true if using with select with table(s)
select COUNT(1),COUNT(*) from tableName

--false if using without select with table(s)
select COUNT(1),COUNT(*)

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

yrb.yogi

--true if using with select with table(s)
select COUNT(1),COUNT(*) from tableName

--false if using without select with table(s)
select COUNT(1),COUNT(*)

First many thanks!

Second, However……I don't know what you are saying——I want to know what the relationship between them and the MOST DIFFERENCE……

And

select COUNT(1),COUNT(*)

still gives me the same answer——1

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

TimoYang

Second, HoweverI don't know what you are sayingI want to know what the relationship between them and the MOST DIFFERENCE

And
select COUNT(1),COUNT(*)
still gives me the same answer——1

There is no difference at all.

If you using Count function without specifying the tablename (properly say, witout using any select query). it will return value one, because

COUNT Function expects some expression as value
Any expression specify, than count function return value 1 (distinct value)

Re: What's the differences among Count(0), Count(1) and Count(SomeField)

So do you mean count(*) and count(1) or count(0)No differences at all