I have table which contains about 470mln of rows. I would like to select data based on date. I have two indices created on this table. One is clustered one and other is non clustered on date column (date is stored as INT). I have simple select statement:
select * from big_table where [date] BETWEEN 20200820 AND 20200828
Estimates generated in query plan are fine, statistics are up to date. This date range should deliver about 5mln of rows. When I provide index hint then this select completes in couple of seconds - without hint it takes couple of minutes to finish.
This is SQL Server 2019 and generaly speaking I noticed that db prefers clustered index scans that using non clustered + keylookups even on bigger tables.
I would rather not using hint because:
- sometimes I select wider ranges where clustered index scan should be desirable
- table is used in view and I cannot supply index hint to the view
Is there any explanation why db is not using NC index it this case?
Links to query plans: