Single Column Indexes Rarely Work
Published: 14th July 2015
Again, following on from my previous posts about Key Lookups, I wanted to dispel a myth that I see used in production systems everywhere… that single column indexes are useful. In the majority of cases they just plain aren’t. Get used to it.

Think of it this way… unless you are avidly querying a table in order to return just one column (or the clustered key of course) and using just that one column in your WHERE clause, then a single column index is not for you.

This is back to the point about Key Lookups… if you are wanting to use your single column index to return any other column from your table then this index will use a Key Lookup. This may not be too bad if you are always doing singleton lookups, but if you want to return even a small set of data then SQL will, in all likelihood, ignore your index entirely.

So here’s a quick piece of test code that we can use:

use tempDB
go

if object_id('tempDB..#lookup') is not null drop table #lookup
go

create table #lookup
(
  
id int identity primary key clustered,
  
firstName varchar(50),
  
lastName varchar(50),
  
address1 varchar(150),
  
city varchar(50),
  
postalCode varchar(50)
)
go

insert into #lookup
select top 10000 firstName, lastName, AddressLine1, city, PostalCode
from AdventureWorks2012.person.address a
join AdventureWorks2012.person.person p
on a.AddressID = p.BusinessEntityID
go

create nonclustered index ix_tempLookup on #lookup(lastName)
go


In this table I have 1 record with the lastName of “Newman”, so let’s look it up alongside the firstName:

select lastName, firstName
from #lookup
where lastName = 'Newman'


This is as we would expect… it used our index.

Now let’s pull out just 1% of the data (100 records) in the table… still a VERY small amount:

update top (99) #lookup
set firstName = 'Belinda',
  
lastName = 'Newman'
where lastName != 'Newman'

update statistics #lookup


SQL switched to a full table scan? For just 1% of the records? That’s because it decided that a Key Lookup would be too expensive and therefore it simply refuses to perform one.

The actual tipping point for SQL Server was, in this case, just 29 records!!! That’s less than 0.3% of the table.

Therefore unless you are returning just 1 column all the time, or returning a VERY small dataset (fractions of a %), then your single column index is likely useless and just taking up space, resource, and IO. Be very careful when you create them.
Comments:
NB: Comments will only appear once they have been moderated.