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.
Table Scan
Forced Index
Table/Index Structure
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
)