Select COUNT(ID) vs Select Count(0)

Question

Post reply

Select COUNT(ID) vs Select Count(0)

BinaryDigit

SSCrazy

Points: 2523
More actions
May 9, 2011 at 3:15 pm

#235987

is there any difference in the time is will take to execute a query where I do a

SELECT COUNT(ID)

FROM TheBigTable

vs

SELECT Count(0)

FROM TheBigTable

The Fastest Methods aren't always the Quickest Methods

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Maybe.

If ID is a nullable column, then count(ID) will be slower than Count(*) or Count(1). If it's not nullable, they are completely equivalent.

http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/

Edit: Apologies, I misread the question first time.

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

BinaryDigit SSCrazy Points: 2523 More actions · Answer 2

BinaryDigit

SSCrazy

Points: 2523

May 9, 2011 at 4:08 pm

#1322016

thank you,

wouldn't a COUNT(*) be longer if you had a BLOB ?

The Fastest Methods aren't always the Quickest Methods

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

No. You're thinking of SELECT * - a shorthand for all columns.

Select * - Return all columns of this resultset.

Count(*) - Count all rows in this resultset regardless of what is in any column. It is totally, 100% synonymous with Count (1), Count(0), Count('My Arbitrary string').

Count(*) is the fastest of the count() forms. It can not be slower than Count(<column name>) which has to additionally check to see if the column is null or not.

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

BinaryDigit SSCrazy Points: 2523 More actions · Answer 4

BinaryDigit

SSCrazy

Points: 2523

May 9, 2011 at 4:48 pm

#1322025

That makes a lot of sense now 😀 didn't think of it like that. thank you =D

The Fastest Methods aren't always the Quickest Methods

Paul White SSC Guru Points: 150457 More actions · Answer 5

GilaMonster (5/9/2011)

Count(*) is the fastest of the count() forms. It can not be slower than Count(column name) which has to additionally check to see if the column is null or not.

Indeed, if the column is defined as NOT NULL, the optimizer replaces COUNT(column) with COUNT(*), since it knows there can't be any NULLs to skip:



SET SHOWPLAN_TEXT ON;

GO

DECLARE @T TABLE (A INT NOT NULL);

SELECT COUNT_BIG(A) FROM @T;

GO

SET SHOWPLAN_TEXT OFF;



  |--Stream Aggregate(DEFINE: ([Expr1004]=Count(*)))

       |--Table Scan(OBJECT: (@T))

There is a very tiny (= immeasurable) compilation overhead to using COUNT(*) versus COUNT([constant]) because the optimizer briefly expands the star into a full column list, before collapsing it again, but there is no difference whatsoever at execution time.

By the way, you might find some people using SUM(1) instead of COUNT(*). Don't do that: they're not equivalent for an empty table (SUM returns NULL, COUNT returns zero).

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi