Hi,

I am comparing two SQL Selects:

select top 1 ORDER_NO from ORDERS where REC_ID <> 'D' and site_no = 0 order by ORDER_NO desc

select top 100 ORDER_NO from ORDERS where REC_ID <> 'D' and site_no = 0 order by ORDER_NO desc

The difference between the two SQL Select is in the number of rows selected.

The table ORDERS has index tags:
on REC_ID
on ORDER_NO
on SITE_NO

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