1

I created a non-clustered index on "last_name" column in the table "Persons"

Select * From Persons
Where last_name = 'Hogg'

So why is the index incapable of returning all the columns simultaneously and instead does a RID lookup? How does indexing work here?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Abhishek Nahar
  • 67
  • 6

1 Answers1

1

The index only covers the column last_name, and only contains data about that column. You can conceptually think about the index that you've described as a series of pairs: (last_name,row), where row is a reference to a particular row in the actual table. The index stores the pairs sorted by last_name, but stores no additional information about the table.

Your query requests all of the columns of Persons. The index is used to locate the row or rows where last_name is "Hogg", but the database has to reference the table to retrieve the additional columns.

What you appear to want is a covering index for the columns of interest. The term "RID lookup" implies SQL Server. Perhaps the question What are Covering Indexes and Covered Queries in SQL Server? and the page it points to: Using Covering Indexes to Improve Query Performance will help.

Community
  • 1
  • 1
sfstewman
  • 5,589
  • 1
  • 19
  • 26
  • But there will be a bucket that points to all the rows with last name "Hogg". So wont it contain the the information about other columns – Abhishek Nahar Nov 11 '13 at 01:11
  • @AbhishekNahar See my revised answer. Based on your description of the index, it will not have the additional information. You can build an index that has this information, but there are tradeoffs. – sfstewman Nov 11 '13 at 02:02