Index scan instead of seek

Question

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.

Answer 1

And the index is on SysDate DESC ?? (Not SysDate ASC...)

---

http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.

Answer 2

Yes it is.

Answer 3

Could you try adding a WHERE clause  - WHERE SysDate < GETDATE()  (or something)....

---

http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.

Answer 4

Yep, that works great in SSIS and in the mobile device.
It seems silly to use a bogus where clause just to get the seek to work but I guess the order by statement doesn't cause the query to use the index.