SQL Server Advent Calendar 4 – Filtered Indexes
Day 4 of my virtual advent calendar, about stuff I like in SQL Server 2008..
Filtered indexes are indexes with a where clause, allowing you to index the part of the table you are interested in. For example if a column can have nulls in you could create an index on it that only indexes the non-null values making the index still relevant but potentially a lot smaller at the same time. better yet if the remaining values are unique you can then make that a unique index.
create unique index production.nullidx
on production.product(code)
where production.product(code) is not null
I have posted about this before, but I wanted to mention it again because there is an occasional problem with filtered indexes returning incorrect answers if there are more than three tables in a join. It’s been fixed by a cumulative update which is here.
Subscribe in a reader" />
