>Results:
>
>1. When I added an index on REC_ID only, the execution plan didn't change at all. The cost was 33% from PK Index Scan and 67% from the RID Lookup (Heap) (I presume that the index REC_ID was not used in Execution plan)
>2. When I added index on REC_ID, ORDER_NO. The same results as above. The new index was not in the execution plan.
>3. When I added index on ORDER_NO, REC_ID. Execution plan shows the cost of 100% from the added index.
>
>So I guess that 3 is the winner.

You have to be careful. OrderNo is the primary key, adding rec_id to it, will slow queries that only need the order_no. I often have a list of records query and then a separate query to get the record data. That separate query only wants the orderNo. Adding the rec_id to the index will affect that.

You could add a new index.


>
>>
>>Imagine you have your table (data file) like this:
>>REC_ID, ORDER_NO, ....
>>
>>and your index is on REC_ID so the index file is
>>REC_ID
>>
>>then SQL server needs to read two files, first it reads the index to answer the "which records" question, getting a pointer to the data table, from where it reads the ORDER_NO field, but if in your index file, in the "Tag" REC_ID you already have the ORDER_NO value, then there is no need to go and read the data file. as the index file is acting as both index and data (ORDER_NO does not participate on the record selection at all, it is just a copy of the data included in the index). Of course then you need to ponder speed vs space, as you are essentially duplicating your data