Optimal Count() operation from a DB using LINQ to SQL

Question

DBAs have told me that when using T-SQL:

select count(id) from tableName

is faster than

select count(*) from tablenName

if id is the primary key.

Extrapolating that to LINQ-TO-SQL is the following accurate?

This LINQ-to-SQL statement:

int count = dataContext.TableName.Select(primaryKeyId => primaryKeyId).Count();

is more performant than this one:

int count = dataContext.TableName.Count();

Answer 1

As I understand it there's no difference between your two select count statements.

Using LINQPad we can examine the T-SQL generated by different LINQ statements.

For Linq to SQL both

TableName.Select(primaryKeyId => primaryKeyId).Count();

and

TableName.Count();

generate the same SQL

SELECT COUNT(*) AS [value] FROM [dbo].[TableName] AS [t0]

For Linq to Entites, again they both generate the same SQL, but now it's

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[TableName] AS [Extent1]
)  AS [GroupBy1]

Answer 2

I know this is an old one but watch out with sql server! Count does not count null values so the two statements may not be equivalent if your primary key field is nullable. see

create table #a(col int null)

insert into #a values (null)

select COUNT(*) 
from #a;

select COUNT(col)
from #a;