SQLTeam.com | Weblogs | Forums

Index Scan & Index Seek issue in SQL Server

The given below query is using "index scan", not using "index seek".

select * from tbl_Vehicle where id not in (1,2,3,4,5,6);

where ID is primary key field. It is seen in Execution Plan that it is using Index Scan, why it is not using Index Seek which is much faster than Index Scan. Is any special technique to use Index seek? Plz resolve the issue.

Why do you want to use a seek?

There is a contiguous range on what is presumably the clustered index. A scan will be more efficient.

SQL doesn't know the specific values to seek to, therefore it has to use a scan. As Ifor noted, a scan will be much more efficient here than seeks anyway.

why it is not using Index Seek which is much faster than Index Scan.

Not always, sometimes a scan is faster.