Why table scan? why?

I have a simple table:

ID int 4, ContentID int 4, Signature Decimal 20, Deleted bit.

A simlpe query:

select Count(Signature) as num from Table WITH (NOLOCK) where ContentID = ### and deleted = 0

What kind of index(s) do I need to avoid a costly table scan?

ContentID as clustered, don't bother with deleted is the normal suggestion.

But you may still get an index scan which effectively on a clustered index is the same as a table scan but more effective than a non-clustered index scan.

The big questions is how many rows do you have? If you ave very few then a table scan is not all that costly if a huge number then clustered on contentid should show a difference.

Be aware that:
select Count(Signature) as num
from Table WITH (NOLOCK)
where ContentID = ### and deleted = 0
is equivalent to:
Select Count(*) as num
from Table WITH (NOLOCK)
where ContentID = ### and deleted = 0
and Signature is not null

quote:Originally posted by mmarovic

Be aware that:
select Count(Signature) as num
from Table WITH (NOLOCK)
where ContentID = ### and deleted = 0
is equivalent to:
Select Count(*) as num
from Table WITH (NOLOCK)
where ContentID = ### and deleted = 0
and Signature is not null

Hmm, that means that I should instead use

Select count(contentID) as num
From table with (nolock)
where contentID = ### and deleted = 0

?

If you need simply number of rows you can use either count(contentID) or count(*) or count(0) or count(3.14142) or...

I agree with Luis. Assuming you dont count(signature), an index on (Content, Deleted) would be the most efficient (for read operations) because it would not require any bookmark lookups. It would be a contiguous disk read.

If you did count(signature), then the most efficient index for accessing this table as a read would be (Content,Deleted,Signature).

Hmm, that means that I should instead use

Select count(contentID) as num
From table with (nolock)
where contentID = ### and deleted = 0
?


index on contentID,deleted
and then fire
set nocount on
Select count(contentID)
From table with (nolock)
where contentID = ### and deleted = 0
set nocount off

Try to create an index on one field and then use count(*) then the optimizer can decide what col to use to get highest performance.

Im not right?