• I like filtered indexes but have ran into a very annoying issue with them once.

    The where clause of a filtered index cannot have OR operators in it (it does support AND, IN and NOT IN). But as soon as you want to filter using more complex conditions you hit a brick wall! Computed columns can also not be used, thus there is no way around this limitation.

    A good use for unique filtered indexes is to apply a business rule on a nullable field by using a where IS NOT NULL. In many cases there is no alternative way of doing this as a regular unique index will allow for only one occurence of a NULL value. This form of filtering improves performance in multiple ways, one is uniqueness versus a regular non-unique index and the other is by accessing less indexed rows and thus operating on a smaller index.

    One final comment:

    Use of filtered indexes only works with non-parameterised where conditions

    A where type = 1 will work, but a parameterised where type = ? with 1 as the parameter value will not. This is because the queryplan will be generated to handle any input value for the parameter. This is implicitly incompatible with a filtered index that singles out a single (set) of values.