I know it is a very old thread, however, quite often, this exactly discussion comes up in different work places...

Is anyone have any material evidence that one syntax is better/faster than another?

If the BoL description for COUNT(*) is not enough (where it is kind of stated that * - doesn't refer to columns in the COUNT function...), try execution plan:

SET SHOWPLAN_ALL ON

GO

SELECT COUNT(1) FROM sysobjects

SELECT COUNT(*) FROM sysobjects

GO

Any difference? I could see none! Check the underlying aggregate function - it is COUNT(*).

If not convinced, try to mesure time:

create table #results (tmms bigint)

go

declare @i bigint

declare @st datetime

set @st = getdate()

select @i = count(*) from dbo.YourLargestTable

insert #results select datediff(ms, @st, getdate())

go 100

select avg(tmms) from #results

Anything? Not even "very small difference"!

I've played quite few matches on very large partitioned and non-partitioned tables in 2005, 2008 and 2012 SQLServer...

My WorldCup's results:

COUNT(*) vs COUNT(1) = draw!

The same goes for EXISTS...

SELECT 'OK' WHERE EXISTS(SELECT 1 FROM sysobjects)

SELECT 'OK' WHERE EXISTS(SELECT NULL FROM sysobjects)

SELECT 'OK' WHERE EXISTS(SELECT * FROM sysobjects)

It doesn't matter what you use, SQL always performs Constant Scan, the rest (index or not index or which index) would be determined by the WHERE or JOIN conditions if any inside of EXISTS query.

So, looks like it is pure personal preference.

I would really appritiate if some one could teach me better (and has reproducible tests to prove its course)