Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Single Column Indexes Rarely Work
Published: Jun 11, 2017
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

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

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

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
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.