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.