Freigeben über

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.

Technorati Tags: sql server 2008,filtered index,cumulative update

Comments