Hello I have a table with a field called UpdateTimeStamp, with a datetime datatype and there is an Index for that field. In the following simple query, I can see that a Clustered Index Scan is performed instead of the expected Index Seek. What could be the reason? select * from MyTable where UpdateTimeStamp>@LastTransferTimeStamp Thanks a lot.
The scan is against the clustered index of your table, which typically is the Primary Key (which is a clustered index by default). You mention that there is an index on the UpdateTimeStamp column, but does the index cover only that column? If there are more columns in the same index, then is UpdateTimeStamp the first one listed in the index definition?
Thanks for your reply. The mentioned Index convers only the UpdateTimeStamp column in ascending order.
Of course, is a different Index created specifically to retrieve faster the rows where UpdateTimeStamp>@LastTransfer And I have just noticed that if I specify the columns to output, and Index Seek (for the referred Index) and an Index Scan are performed. select PKColum, OtherColumn, UpdateTimeStamp from MyTable where UpdateTimeStamp > @LastTransfer I presume that depending also of the columns being retrieved, Indexes are used.
An Index Scan is still better than a Table Scan, so you do have the benefit of having the index. If you were filtering for a specific date, or a date range, then you would probably get an Index Seek on the index covering UpdateTimeStamp. With a < or > operator, the query engine will guess about one third of the rows will match the criteria, so no point in doing a seek since it needs to read a lot of rows for the results anyway.