I have a compact 3.5 SP1 database.
My query is
SELECT TOP(1) SysDate FROM Violator ORDER BY SysDate Desc
The SysDate column is a datetime with unique values.
I have an index on the Sysdate column which I created using SSMS.
When I run the query the execution plan shows it is using an index scan instead of a seek.
There are 191,000 rows in the table.
It is using a scan because there are too few rows to use a seek?
This query takes < 1 sec in SSMS but it takes 35 sec in a mobile device emulator which is what I am trying to fix with the index.