Filtered Indexes

Filtered Indexes first appeared in SQL Server 2008. As the name implies, they can create a subset of a full index. They improve query performance, reduce index maintenance costs, and reduce index storage. Also, an indirect benefit is being able to enforce unique constraints for primary or active records.

Filtered Indexes are a great addition to SQL Server. For data warehouse dimension tables, you can filter on the active flag and greatly reduce the index size and get a much better query plan. As the warehouse grows, the active records can quickly become less than 1% of the table. In the mortgage industry, you will have a primary title holder and the co-signers. A third example would be health insurance. There is a card holder and dependents on policies with a family subscription. Since most processes go through the card holder, you want to make this as fast as possible.

Let’s use a typical family insurance policy. Below is a sample of 3 policies with one or more members per policy.

The standard index can be created with:

CREATE NONCLUSTERED INDEX IX_PolicyMember_PolicyID_IsPrimary

ON [dbo].[PolicyMember] (PolicyID, IsPrimary)

This will work; but, will be less efficient than a filtered index if you have of millions of policies and tens of millions of members.

To create a Filtered Index, we only have to add a WHERE clause.

CREATE NONCLUSTERED INDEX IX_PolicyMember_PolicyID_IsPrimary

ON [dbo].[PolicyMember] (PolicyID, IsPrimary)

where IsPrimary = 1;

This will get us the benefits mentioned earlier; but, it will not stop us from adding a second Primary person to the policy.

INSERT INTO [dbo].[PolicyMember]

           ([PolicyID]

           ,[MemberID]

           ,[IsPrimary])

     VALUES

           (100, 7, 1);

Now we have two records with a Primary indicator of 1.

This will cause problems. We would have to ensure the application and any manual updates are performed correctly.

With one more little change we can get the increased performance with a constraint of only one primary record per policy by adding a unique filtered index. First let’s delete the last row added.

Delete PolicyMember

 where MemberID = 7;

Create a unique filtered index:

CREATE UNIQUE NONCLUSTERED INDEX IX_PolicyMember_PolicyID_IsPrimary

ON [dbo].[PolicyMember] (PolicyID, IsPrimary)

where IsPrimary = 1;

Now if we try to add member 7 as primary, we get an error.

INSERT INTO [dbo].[PolicyMember]

           ([PolicyID]

           ,[MemberID]

           ,[IsPrimary])

     VALUES

           (100, 7, 1);

Cannot insert duplicate key row in object 'dbo.PolicyMember' with unique index 'IX_PolicyMember_PolicyID_IsPrimary'. The duplicate key value is (100, 1).

The statement has been terminated.

We are always looking for ways to increase performance. With this recently added feature, we can get a lot of benefit without application changes. Take a look at some of your performance bottlenecks and see if they are good candidates for the Filtered Index.