Why reading more rows if I add non clustered index

Guru 2016-04-15 18:56:47

The query plan shows Hash Match and to avoid that I have added index on TermGroupID. But the # reads increased to 2000 rows.

Aaron Bertrand 2016-04-15 19:13:58
Can you show the index you added (it's not obvious since TermGroupID exists in more than one table, and it's not clear what the actual definition of the index might be). Can you also show the plan you were getting without that index?
Guru 2016-04-15 19:17:50
Without index, you can see the diagram with plan tree. I will email you the plan with TermGroupID index.
Guru 2016-04-15 19:22:17
I have emailed. On the plan diagram, please look KeyLookup – TermGroupDetail.

Here are the table columns of TermGroupDetail:

ID int identity(1,1)
TermGroupID (FK, int, not null),
TermOdometerID (FK, int, not null),
EffectiveDate (Date, not null)
ExpirationDate (Date, not null)

ID, TermGroupID, TermOdometerID are the Primary Key

Added non clustered index on TermGroupID and not added any column into that.

Thanks,
Guru

SQLkiwi 2016-04-17 05:03:10
The new nonclustered index also needs to have TermOdometerID as a key or included column.