Filtered Indexes on SQL Server

Filtered Indexing can be a good strategy to lower the amount of data you are looking at.  Lets say you have a table, like we do, called Members which has 50 million records in it.  Lets say you know that there is an active flag in it, and that for 16 million of those records the flag is set to one and the rest are zero.  If you have a query that is grabbing the Last and first name, AND it wants Active = 1, when then this type of index might be for you:

CREATE INDEX IX_Members_byLastNameFirstName_filtered on Members

(

    LastName,

    FirstName,

    Active

)

Where (Active = 1)

Now, there are some caveats, and that's what I want to point out in this blog post.  Looking at the Index I have included a column that might not really need to be in there, but the optimizer might want it there... Or not... That comes down to testing.. And sometimes it can be really frustrating when SQL Server just doesn't use your filtered indexes for whatever reason.  There are plenty a posts out there in Blog land about these caveats so feel free to Internet Out -- 

What I want to focus on here are some important settings that need to be in place before running down this road towards the filtered index party:

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET NUMERIC_ROUNDABORT OFF

Now it's possible that your sprocs may have some settings that don't fall inside these restrictions. I found several that had ANSI_NULLS off at the time, and here is what I cobbled together to get that list:

SELECT 
    a.ROUTINE_NAME, 
    a.ROUTINE_TYPE, 
    OBJECT_DEFINITION(object_id(a.ROUTINE_NAME)) as SprocText, 
    b.uses_ansi_nulls 
FROM 
    INFORMATION_SCHEMA.ROUTINES as a 
JOIN sys.sql_modules as b 
ON.routine_name = object_name(b.[object_id]) 
WHERE 
    OBJECT_DEFINITION(object_id(ROUTINE_NAME)) LIKE '%Table_With_Filtered_Index%' and b.uses_ansi_nulls = 0