Indexes often double as constraints. Each combination of values must only appear once among the columns of an index defined as UNIQUE. The “filtered” index allows some refinement. Suppose that in one column you won’t allow a specific value to appear more than once, but you don’t mind other values being duplicated.
A good example would be the IS_LATEST_VERSION column in a data warehouse table. Continue reading Sql Server Filtered Index as a Unique Constraint on a Specific Value