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 & 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 & 1) AS BIT))
CREATE INDEX MYTABLE_IX_FLAG_BIT_1 ON MYTABLE (FLAG_BIT_1)
SELECT * FROM MYTABLE WHERE FLAGS & 1 = 1
SELECT * FROM MYTABLE WHERE FLAG_BIT_1 = 1
SELECT COUNT(*) FROM MYTABLE WHERE FLAGS & 1 = 1
SELECT COUNT(*) FROM MYTABLE WHERE FLAG_BIT_1 = 1
Best Answer
Index on computed (AKA virtual) column
Demo