Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2010-03-24 : 15:34:42
|
| Is...select count(*) from MyTableslower than...select count(MyTableID) from MyTable |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-24 : 15:37:32
|
no
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-24 : 17:18:26
|
Supposedly using "*" in COUNT(*) allows SQL to choose any column that will be most optimal in making the count.Using "*" is frowned on, so many people think they should use something different.SELECT COUNT(1) or SELECT (SomeColumn) are often used instead.I think, nowadays, that SQL optimises the various variants to get the same Query Plan.However, technically SELECT COUNT(MyColumn) will count the number of rows where (MyColumn) is NOT null.Perhaps SQL will short-circuit that where MyColumn is defined in the table as being NOT NULL - but maybe not (in which case an Index or Table scan will be used to check the values - which will be slow ...)Other place where people seem to try no to use "*" is in EXISTS:SELECT ... FROM ... WHERE EXISTS (SELECT * FROM OtherTable WHERE ...) again, this is a clue to SQL to use whichever column suits it - and not an instruction to retrieve all columns! |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-03-24 : 17:51:27
|
| Just trying to help distil down some information..Yes, COUNT(ColName) will not could NULLs.If there is an index (Clustered or Non) using SELECT COUNT(*) will use an index. If there is not a Clustered index then SELECT (ColName) from a table without an index on ColName will cause a table scan. But, Webfred is correct that COUNT(*) will (theoretically :) ) never be slower than SELECT (ColName). But, if you are using a WHERE clause, then that is whole other can of worms. |
|
|
|
|
|
|
select count(*) - performance???