Saturday, February 28, 2009

Foreign Keys can reference UNIQUE indexes (without constraints)

by kimberly.nospam@nospam.sqlskills.com (kltripp) via Kimberly L. Tripp on 2/28/2009 5:37:00 PM

Something I learned while the SQL Server 2008 Internals book was in tech edit (thanks to our *awesome* tech editor Ben Nevarez - who, unfortunately, does not have a blog or anything...yet! (well, I'm hopeful)), was that you can use a FOREIGN KEY constraint to reference a UNIQUE index - one without a PRIMARY KEY or UNIQUE key constraint. At first glance this might seem like something relatively insignificant but in terms of reducing indexes and/or consolidating indexes it offers something that constraints do not. When you create a UNIQUE index you can use INCLUDE to reference (and include) non-key columns in the leaf level of an index. This offers more choices for covering and if you want to cover a query using INCLUDE but also have a UNIQUE column(s) as the key - you can do that with a regular index but not with a constraint based index. So, that got me thinking even more - can I use a UNIQUE index with INCLUDE and even a filters - from a FOREIGN KEY. My guess was that it probably wouldn't work because it would be too costly to have to verify it on every referencing row BUT, I did have hopes that a filter of IS NOT NULL would work. However, it does not. ;-( 

So, you CAN reference a UNIQUE index with INCLUDEd columns but not filters. Even that's really cool!

And, when you start your spring cleaning - try and cleanup and/or consolidate some of those redundant indexes!!

Cheers,
kt