I have a table with 145 million rows
CREATE TABLE [dbo].[RFTest](
[SnapshotKey] [int] NOT NULL,
[SnapshotDt] [datetime] NOT NULL,
[LoanNum] [int] NOT NULL,
[GLSourceSystem] [varchar](10) NOT NULL,
[FlowDescription] [varchar](30) NULL,
[Account] [varchar](30) NULL,
--- plus 20 more column
)
The table is partitioned on SnapshotDt.
I added following indexes on my table:
create clustered index ci on RFTest (SnapshotDt, SnapshotKey, LoanNum)
create nonclustered index nci on RFTest (SnapshotDt, SnapshotKey, LoanNum)
include ([GLSourceSystem],[Account],[FlowDescription])
I ran below query: (I use top 100 for test as the whole table will take long time if I wanted to run it)
select top 100 *
from RFTest with (index(ci)) -- force index
where LoanNum = 2712
select top 100 *
from RFTest
where LoanNum = 2712
LoanNum column exists in both indexes, part of key in clustered and included in non-clustered.
Execution plan shows the engine choose non-clustered "nci" index, NOT the clustered one.
I like to know why.
Clarification:
To me in both case SQL read the same amount of data. and LoanNum is in both index and BTW, the LoanNum is part of key so It seems to me that it makes more sense if it use clustered index.
The indexes are exactly as I posted. There were some comments in queries, when I captured plan. the query you see in the post is correct. I don't want to keep both index, I was trying to see which one performs better and the question came to me.