Mohit K. Gupta (5/1/2009)
If you are just using non-clustered indexes and heap; it will first cause a RID Lookup anytime you access anything in that table via a Index Seek. That is when an index seek operation completes it will have to go back to main heap to get the data. In addition when you are inserting, deleting, and updating records in the table the heap can become fragmented and there is no way to defrag it because of how heap are stored. So the access speed, updates, and deletes can take long time.
Hi there, I have also found a large table with no clustered indexes and four non-clustred indexes. I can see that one of these non-clustered indexes is being used in the query, but I don't understand what happens then with the RID Lookup - since there is no clustered index does SQL Server have to scan the whole heap? In which case, why bother with an index at all! Hope I'm making sense 🙂 I'm thinking of adding a clustered index to the table, but it's from a 3rd party app, so I need to be very careful (i.e. break the test system first!).