>>The execution plan for the top (select 1) shows that 33% cost is from Index Scan of ORDER_NO Non-clustered index and 67% from the RID Lookup (Heap) ORDERS
>>
>>The execution plan for the second SQL Select (select 100) shows 1% cost from index Scan ORDER_NO and 99% from the RID Lookup (Heap) ORDERS.
>>
>>My questions:
>>1. Is it bad that the second execution plan only uses 1% of the index?
>>2. Does RID Lookup (heap) ORDERS means that this is based on the physical scan of the table?
>>3. Why does the second SQL Select uses more RID Lookup?
>>
>>TIA
>
>I decided (for testing) to add a clustered index on ORDER_NO. After I created this clustered index the execution plan shows 99% of the cost goes for this clustered index. I think this is a good thing, no? Does it mean that every table has to have a clustered index?
There is a short blog post
http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/best-practice-every-table-should-have-a/ that may be a starting point.
If it's not broken, fix it until it is.
My Blog