Best Way to Index an INT Column Used for BitWise Queries
Index on computed (AKA virtual) column



Demo

with t(i) as (select 0 union all select i+1 from t where i&lt;999) 
select 1+t0.i+1000*t1.i as FLAGS into MyTable from t t0,t t1 option (maxrecursion 0)




alter table MyTable add flag_bit_2 as (cast ((FLAGS &amp; 4) as bit))
create index MyTable_ix_flag_bit_2 on MyTable (flag_bit_2)




select count(*) from MyTable where flag_bit_2 = 1