Esimated Query plan and index

Transact-SQL
1

i have a table, with one non Clustered index on id.
i maje quries on this table on the datetime coulmn (table has 1.8 million rows and grow all the time)
the search is very fast (no index on this column).
when i look at the execution plan ot show it scaned the clustered index.

  1. why it scanned the clustered index why the "where" caluse was on another column.
  2. how come the seach so fast? because of the statistics?

this is the estimated execution plan i see :

respone

2

The clustered index is the table itself. SQL is doing a full table scan because there is no index it can use.

For this table, you should cluster the table on the datetime first, then on id, to make the clus index unique.

CREATE UNIQUE CLUSTERED INDEX CL_xxxesponses ON dbo.xxxesponses ( datetime, id ) WITH ( FILLFACTOR = 99 ) ON [PRIMARY]; /change filegroup name from PRIMARY to something else if you prefer/

3

Hi, the datetime is not unique, and on id i need the autoincrement value.