Gerard Silveira (3/9/2014)
Thanks for your reply Jack , so that would mean that SQL Server would have to actually do a row lookup to return the value for the Orderno, and would not be able to use the value present within the index structure ?
No. If you have a clustered index on orderNo, any index you create will include the clustering key as part of the key. So if you create an index on selectedUser the key values for the index will be:
selectedUser + orderNo
and the leaf level would be:
selectedUser + orderNo + [included columns]
So a query that just returns selectedUser and orderNo may not have to navigate all the way down to the leaf level to return the data.
Both of the indexes you propose are covering indexes for any query that includes the 3 columns listed in the table, if there is a clustered index on orderNo. The only benefit to actually listing orderNo in the included list is that, if you ever change the clustered index, you still have a covering index for any queries that require those 3 columns.