Best Way to Index an INT Column Used for BitWise Queries What is the best way to index a column for an efficient look-up based on a bitwise comparison in the WHERE clause e.g. 

SELECT ID, FLAGS, NAME FROM TABLE WHERE FLAGS &amp; 4 = 4


I am in a SQL server 2012 environment. 

I understand that an index on the FLAGS column won't be used in this type of query.

I know the other option is to break the FLAGS column out into individual columns, but I wanted to avoid that because in some cases, that would mean 25+ new columns, each of which would need a combination of indexes to support the common queries.

Are there any ways to index the single column so that the bitwise queries are more efficient?

I tried using a computed column with an index as suggested by dudu below, but when I used in on a table which has a clustered index, and start selecting columns (instead of just select count(*) it no longer uses the index on that new computed column.

SELECT 
* 
INTO 
    MYTABLE
FROM 
(
    SELECT 'A' COL_A, 'A' COL_B, CAST(129 AS INT) FLAGS UNION
    SELECT 'B' COL_A, 'D' COL_B, CAST(129 AS INT) FLAGS UNION
    SELECT 'C' COL_A, 'S' COL_B, CAST(129 AS INT) FLAGS UNION
    SELECT 'D' COL_A, 'F' COL_B, CAST(129 AS INT) FLAGS UNION
    SELECT 'E' COL_A, 'T' COL_B, CAST(128 AS INT) FLAGS UNION
    SELECT 'F' COL_A, 'D' COL_B, CAST(128 AS INT) FLAGS UNION
    SELECT 'G' COL_A, 'D' COL_B, CAST(128 AS INT) FLAGS UNION
    SELECT 'H' COL_A, 'X' COL_B, CAST(128 AS INT) FLAGS 
) SRC

CREATE CLUSTERED INDEX IX_CLU_A ON MYTABLE(COL_A)



ALTER TABLE MYTABLE ADD FLAG_BIT_1 AS (CAST ((FLAGS &amp; 1) AS BIT))
CREATE INDEX MYTABLE_IX_FLAG_BIT_1 ON MYTABLE (FLAG_BIT_1)

SELECT * FROM MYTABLE WHERE FLAGS &amp; 1 = 1
SELECT * FROM MYTABLE WHERE FLAG_BIT_1 = 1

SELECT COUNT(*) FROM MYTABLE WHERE FLAGS &amp; 1 = 1
SELECT COUNT(*) FROM MYTABLE WHERE FLAG_BIT_1 = 1
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