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:
if object_id('tempDB..#lookup') is not null drop table #lookup
create table #lookup
id int identity primary key clustered,
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
create nonclustered index ix_tempLookup on #lookup(lastName)
In this table I have 1 record with the lastName of “Newman”, so let’s look it up alongside the firstName:
select lastName, firstName
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.