Indexes over simplified

There is a ton of literature on how SQL Server indexes work, and I won't repeat all that here. But I want to over-simplify it to help beginners understand what kinds of indexes they should be adding to their queries.

Lets say you have a query like this:

SELECT BusinessEntityID

     , FirstName

FROM Person.Person

WHERE LastName = 'Smith'

What type of index should you add to this query? 

Well, because its looking at three columns (BusinessEntityID, FirstName and LastName), you'll want an index to cover all three. 

But what type? There are two types of covering index:

• Key Column cover: A covering index where the key to the index is made up of all the columns
• Include Column cover: A covering index where the key to the index is made up of only one column (you can specify more), but the rest are included.

A Key column index over Lastname and FirstName (the order is important) looks like this:

• Jones
• Abraham
• Isaac
• Smith
• Bob
• Trudy

An include index with a key of Lastname including Firstname, looks like this:

• Jones - Isaac
• Jones - Abraham
• Smith - Bob
• Smith - Trudy

In short, a Key Column index is sorted by both LastName, and then FirstName. But it includes the firstname within the index structure itself, so that after having found the key (LastName), it doesn't have to go back to the table to find the other data (FirstName).

So for the following query, the ideal index would have its key on LastName, since that is what SQL Server will have to search through the data to find first, since we're filtering on it, and then once its found all rows with LastName of 'Smith', SQL Server will have to fetch the FirstName and BusinessEntityID for these rows, so we can include them in the index, to make it faster for SQL Server to return them.

SELECT BusinessEntityID

     , FirstName

FROM Person.Person

WHERE LastName = 'Smith'

We could also have created an index with three columns LastName>FirstName>BusinessEntityID. This would be just as fast. In fact, it would help out in other scenarios too! For example, if we added an "ORDER BY LastName, FirstName" clause to the end. Because the FirstName would be indexed (i.e. arranged in order), not merely included.

But what if we change the query to this?

SELECT LastName 

     , FirstName

FROM Person.Person

WHERE FirstName = 'Bob'

Because the key column index is ordered by LastName first, but the query needs to retrieve rows with FirstName first, the query will have to scan through every row of the index to find the data, making it less efficient.

But even though this is filtering on FirstName, it can actually still use the rowcover index we created. Of course, this won't be as fast as the query before, but SQL Server will still be able to make use of this index, making it a better option than having a key on Lastname and including firstname, which would not be able to be used if we put Firstname in the WHERE clause.