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!!