0

Can it have a negative performance impact to INNER JOIN two tables via PrimaryKey columns while one table has a CLUSTERED Index on its PrimaryKey column whereas the other table has a NON-CLUSTERED index on its PrimaryKey column?

Lajja Thaker
  • 2,031
  • 8
  • 33
  • 53
pencilCake
  • 51,323
  • 85
  • 226
  • 363
  • if I well understand the question, performance will depend on the fields (of the table having the non clustered index) of the select clause : are they all part of the non clustered index or not ? If not the data engine will have to load in memory the pages of index AND the pages of data. – tschmit007 Sep 18 '12 at 08:18
  • So you say it matters whether the select clause contians the fields that are part of INDEXes? – pencilCake Sep 18 '12 at 09:08
  • 1
    yes that's why the [INCLUDE](http://msdn.microsoft.com/en-us/library/ms190806.aspx) instruction exists and also the notion of covering index. – tschmit007 Sep 18 '12 at 09:11
  • By the way, as often, selection gain is insertion loss. From here it is a question of balance between reads and writes. – tschmit007 Sep 18 '12 at 09:15

0 Answers0