I got a recommendation for SQL Server for this query:
SELECT field1 FROM table WHERE ( field2 < getdate() - 2557)
Obviously the recomendation was
CREATE INDEX idx_name on TABLE (field2) INCLUDE (field1)
But there was an index with only one column, and the same include that I needed, so I created an index with
field2 and the
fieldx from the other index, with the same include
field1 because I will drop the old one and stick only with the new one with 2 fields.
when I ran the query again, why is it suggesting me to create again the index with only
CREATE INDEX idx_name on TABLE (field2) INCLUDE (field1).
the index has 2 columns and one include. shouldn’t the query be able to do a seek on the table?
For now, it’s really really faster, but it’s still doing a SCAN.
I’ve never seen this behaviour. I usually add columns on indexes that looks like the ones suggested for SQL Server and I can get a seek everytime.
table has 700k rows.