RID|Key lookup vs Index Seek? [modified]
-
Hi I understand what index seek means - but not sure about distinction between Index Seek, RID Lookup (Heap based what the hell...) and Key Lookup (clustered index lookup) From this reference: [^] RID Lookup is a form of a bookmark index lookup on a table without a clustered index (a heap). > So both clustered and non-clustered is on the disk and SQL server actually build another index in memory/heap?! And all RID's (Row ID) are stored in volatile memory!? key lookup is a bookmark lookup on a table with a clustered index. > So how is it different from Clustered Index Seek? I'm a bit unclear on seek vs lookup, but seems like "non-clustered seek" (non-clustered ONLY)[^] fetches only the RID, database engine still need to perform a "lookup" by RID to fetch the actual values of selected columns. For "clustered index seek", leaf is actual data so there should never be lookup for rows fetched via "clustered index seek" (thus no idea what a "Key Lookup" is about) Similar question to http://www.web-shelf.com/microsoft.public.sqlserver.relationalserver.performance/index-seek-with-lookup-vs-index-seek-with-include-thread-ID1202.aspx[^] Adding to confusion, from MSDN[^]- so RID is in heap (where? All rows RID in heap/memory!?) and Key lookup is done via clustered index on the disk? HEAP (aka "Base Table) is not Clustered Index which is stored on disk. RID Lookup is a bookmark lookup on a heap using a supplied row identifier (RID). The Argument column contains the
-
Hi I understand what index seek means - but not sure about distinction between Index Seek, RID Lookup (Heap based what the hell...) and Key Lookup (clustered index lookup) From this reference: [^] RID Lookup is a form of a bookmark index lookup on a table without a clustered index (a heap). > So both clustered and non-clustered is on the disk and SQL server actually build another index in memory/heap?! And all RID's (Row ID) are stored in volatile memory!? key lookup is a bookmark lookup on a table with a clustered index. > So how is it different from Clustered Index Seek? I'm a bit unclear on seek vs lookup, but seems like "non-clustered seek" (non-clustered ONLY)[^] fetches only the RID, database engine still need to perform a "lookup" by RID to fetch the actual values of selected columns. For "clustered index seek", leaf is actual data so there should never be lookup for rows fetched via "clustered index seek" (thus no idea what a "Key Lookup" is about) Similar question to http://www.web-shelf.com/microsoft.public.sqlserver.relationalserver.performance/index-seek-with-lookup-vs-index-seek-with-include-thread-ID1202.aspx[^] Adding to confusion, from MSDN[^]- so RID is in heap (where? All rows RID in heap/memory!?) and Key lookup is done via clustered index on the disk? HEAP (aka "Base Table) is not Clustered Index which is stored on disk. RID Lookup is a bookmark lookup on a heap using a supplied row identifier (RID). The Argument column contains the
I think that you've been thrown off by the meaning of the word "heap". Although it usually means what you think (i.e. an in-memory data structure[^]), in the context of the discussions that you quoted it means a heap-based table[^]. Anyway, Key Lookup is logically identical to RID Lookup. Physically, there are significant differences, but nine times out of ten you can count them with the rest of unimportant implementation details.
-
I think that you've been thrown off by the meaning of the word "heap". Although it usually means what you think (i.e. an in-memory data structure[^]), in the context of the discussions that you quoted it means a heap-based table[^]. Anyway, Key Lookup is logically identical to RID Lookup. Physically, there are significant differences, but nine times out of ten you can count them with the rest of unimportant implementation details.