• roger.plowman (8/24/2011)


    My database design requires the use of bit fields, the majority of which will be false, and it would be nice to have a filtered index for bit fields that are true.

    Does this work? Or will the optimizer skip over bit field indexes?

    Give it a try. Run this and look at the execution plan.

    if OBJECT_ID('dbo.test') is not NULL

    drop table dbo.test

    create table dbo.test (i int primary key,

    isinactive bit

    )

    create index FIX_Test_Active on dbo.test (i)

    where isinactive = 1

    go

    with i as (select top 8000 ROW_NUMBER() over (order by a.object_id) id

    from sys.columns a, sys.columns b)

    insert into test

    select id,

    case

    when (id % 2) = 0 then 1

    else 0

    end

    from i

    select i from dbo.test where isinactive = 1

    You can also turn stats IO and CPU time on and see how much of a time savings there was.