It looks like a scan count > 0 occurs when access occurs against a non unique index or a table scan.
Probably because a seek against a non-unique non-clustered index is not guaranteed to bring back one record so the engine may be programmed to explicitly iterate the subset returned.
I have been able to get scan count =0 when seeking for 1 record against a unique index when using the full key of that index
e.g. WHERE COLUMN13 =1 ,and the unique index is only on COLUMN13
When I use something like
Select ..... WHERE COLUMN13 IN (1,2,3)
I get a scan count of 3 even if the index is unique on COLUMN13.
Because of the last point, I would not use scan count to diagnose issues.
Test scenarios below, against AdventureWorks2008R2
DROP INDEX [AK_Employee_NationalIDNumber] ON [HumanResources].[Employee] --Get rid of this existing index
GO
create unique nonclustered index [AK_Employee_NationalIDNumber] on humanresources.Employee (NationalIDNumber)
go
set statistics io on
select NationalIDNumber from humanresources.Employee where nationalidnumber = '295847284'
set statistics io off
go
drop index humanresources.Employee.[AK_Employee_NationalIDNumber]
go
create nonclustered index [AK_Employee_NationalIDNumber] on humanresources.Employee (NationalIDNumber)
go
set statistics io on
select NationalIDNumber from humanresources.Employee where nationalidnumber = '295847284'
set statistics io off
go
drop index humanresources.Employee.[AK_Employee_NationalIDNumber]
go
create unique nonclustered index [AK_Employee_NationalIDNumber] on humanresources.Employee (NationalIDNumber)
go
set statistics io on
select NationalIDNumber from humanresources.Employee where nationalidnumber in ('295847284',
'245797967',
'509647174',
'112457891',
'695256908',
'998320692',
'134969118',
'811994146')
set statistics io off
go
drop index humanresources.Employee.[AK_Employee_NationalIDNumber]
go
create nonclustered index [AK_Employee_NationalIDNumber] on humanresources.Employee (NationalIDNumber)
go
set statistics io on
select NationalIDNumber from humanresources.Employee where nationalidnumber in ('295847284',
'245797967',
'509647174',
'112457891',
'695256908',
'998320692',
'134969118',
'811994146')
set statistics io off
go
drop index humanresources.Employee.[AK_Employee_NationalIDNumber]
go
create unique nonclustered index [AK_Employee_NationalIDNumber] on humanresources.Employee (NationalIDNumber)