SQL 202-Covered Indexes

By this point you should feel pretty comfortable with what an INDEX is and what it does for your queries.  Basically it provides the server to find your data rows more quickly by creating a look-up to find key values.  When teaching indexes I usually open the book closest to me, and show them the index in the back.  If you’re looking for the page about hybrid cars, turn to page 251.  I then turn to page 251, and show the page to the class.

Fast right?

Absolutely, but you can take this one step further.  A phone book is an index too, but it has extra information, phone numbers.  This is a lot like a covered index.  We index one column (or a couple columns), and we INCLUDE extra columns.  Then when the server hits an index it doesn’t have to leave the index and go to the table in order to retrieve that included column.

Consider this table:

CREATE TABLE contacts (
     contactID INT
   , firstname VARCHAR(255)
   , lastname VARCHAR(255)
   , phoneNumber VARCHAR(10)
   , <10, 20 more columns> )

We have a fairly wide table containing our contacts.  Fairly often we want to lookup  phone numbers by name (first and last). I’ll set up variables for the parameters of our lookup.

SELECT phoneNumber FROM contacts WHERE firstname = @firstname AND lastname = @lastname

We could make it pretty fast if we had an index on first and last name.

CREATE INDEX ix_contacts__firstname_lastname ON contacts (firstname, lastname)

Looking at the stats this index is pretty fast:

results came back in 60ms
Table 'contacts'. Scan count 9, logical reads 9062, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Pretty good, but like I said we could do better.

CREATE INDEX ix_contacts__firstname_lastname__inc_phonenumber ON contacts (firstname, lastname) INCLUDE (phoneNumber)

Now, look at the results:

results came back in 1ms
Table 'contacts'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Much better!  By not having to go back to the original table, we save a ton of reads.  And that’s what the covered index is all about Charlie Brown.

I’m not telling you it’s always the answer, but if you know what covered indexes are, and you know how to apply them… You’ll be surprised how often it is the answer.

Any questions?  If so, send them in. I’m here to help!