>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
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?
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham