I know when it comes to using an index or a table scan, SQL Server uses statistics to see which one is better.
I have a table with 20 million rows. I have an index on (SnapshotKey, Measure) and this query:
select Measure, SnapshotKey, MeasureBand from t1 where Measure = 'FinanceFICOScore' group by Measure, SnapshotKey, MeasureBand
The query returns 500k rows. So the query selects only 2.5% of the table's rows.
The question is why SQL Server does not use the nonclustered index I have, and uses a table scan instead?
Statistics are updated.
Good to mention that the query performance is good though.
CREATE TABLE [t1]( [SnapshotKey] [int] NOT NULL, [SnapshotDt] [date] NOT NULL, [Measure] [nvarchar](30) NOT NULL, [MeasureBand] [nvarchar](30) NOT NULL, -- and many more fields ) ON [PRIMARY]
No PK on table, as it is a data warehouse.
CREATE NONCLUSTERED INDEX [nci_SnapshotKeyMeasure] ON [t1] ( [SnapshotKey] ASC, [Measure] ASC )