q and a

I had a question recently in which someone asked the following:


"I'm trying to determine what the overall performance difference would be in the following situation…

Assume I have a large table, 500M records that have a non-unique RecordID column (eg. RecordID (BIGINT), SubID (BIGINT), Name, Detail)

I'm only ever going to select * from Table where RecordID = ?

If I create a clustedIndex on RecordID the execution plan shows only two steps

Select + Clustered Index Seek

If I leave the table as a HEAP and create a no-clustered index on RecordID the execution plan shows 5 steps

Nested Loops Inner Join <- ((Compute Scaler + Index Seek) + RID Lookup)

Clearly I'm going to get better insert performance when inserting into the HEAP, especially when page spits are required on the clustered index.

What I don't know is what the select performance difference would be under load.

e.g. I can expect the RID lookup to be X% slower than the Clustered Index Seek"


The answer I gave was as follows: