Below is my query and i have two different NC indices. Now when i have the first index, below query is doing a cluster index scan and if i have the second index query is doing a seek. I have two questions.
Does order of index matter on the columns which you are joining and using a where condition.Which column should come first , should i use the column where i am using '='operator or the one on which i am joining
Create Index Test_NC1 On RcrdSt
Create Index Test_NC2 On RcrdSt
select Rkey,Tid from Rgrst
inner RcrdSt RTS_P
on R.ID = RTS_P.RKey
AND RTS_P.TID = 11