Query Performace

Question

Query Performace

abmore

Hall of Fame

Points: 3628
More actions
November 26, 2007 at 6:30 am

#181565

hi !,
I have written 1 Query in 3 ways.
Select COUNT(1) FROM table1
Select COUNT(*) FROM table1
Select COUNT(Col1) FROM table1
My Question is I m getting the same execution plan for the queries :w00t:. But I want to know the difference betw. these queries.
I knw y this is happing but want to knw the reasons / solutions form all of you...
thnks in advance..

Viewing 8 posts - 1 through 7 (of 7 total)

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Abhijit (11/26/2007)
hi !,
I have written 1 Query in 3 ways.
Select COUNT(1) FROM table1
Select COUNT(*) FROM table1
Select COUNT(Col1) FROM table1
My Question is I m getting the same execution plan for the queries :w00t:. But I want to know the difference betw. these queries.
I knw y this is happing but want to knw the reasons / solutions form all of you...
thnks in advance..

This is a fairly common optimization "trick". It does not matter what data you are using in COUNT. This is probably the only place where the * should be allowed in a select statement 🙂 The query optimizer realizes this, and ignores the column or * you specify (and in such cases the data may not even be looked up, it is sufficient to go through an index. Note that this optimizations is not always applicable, you may have something like: count(distinct Col1).

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Not quite.

Count(*) or count(1) or similar will give you the number of rows in the table. Count(ColumnName) will give you the number of rows where that column is not null.

If you're using the pk column, or a not null column then you'll get the same answer. If you're using any other column, then you may not get the same answer.

Easy to see this. Run these and note the difference in the value.

select count(*) from sys.dm_exec_sessions

select count(nt_domain) from sys.dm_exec_sessions

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

GilaMonster (11/26/2007)
Not quite.
Count(*) or count(1) or similar will give you the number of rows in the table. Count(ColumnName) will give you the number of rows where that column is not null.
If you're using the pk column, or a not null column then you'll get the same answer. If you're using any other column, then you may not get the same answer.
Easy to see this. Run these and note the difference in the value.
select count(*) from sys.dm_exec_sessions
select count(nt_domain) from sys.dm_exec_sessions

Hi Gail,

you are right. However in this case the execution plans actually will be different. The "Table Valued Function" will have the object set to [SYSSESSIONS] and [SYSSESSIONS].nt_domain respectively.

If the column is not nullable, count(*) is usually replaced, as in the following example:

drop table foo

create table foo (a int not null constraint foo_pk primary key)

select count(*) from foo

select count(a) from foo

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Sure. I was pointing out that the optimiser doesn't (and can't) always ignore the column specified. Your reply seemed to say that count(*) and count(column) are equivalent, which they aren't' always.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

GilaMonster (11/26/2007)
Sure. I was pointing out that the optimiser doesn't (and can't) always ignore the column specified. Your reply seemed to say that count(*) and count(column) are equivalent, which they aren't' always.

Thanks,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

abmore Hall of Fame Points: 3628 More actions · Answer 6

Gail I didn't get you, can u tell me then why there is no diff in Execution plan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

In your case, probably because the column is not nullable, or because there's an index on that column and SQL's decided that it's the best way to do the counts.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass