6

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
Improve this question

1 Answer 1

Reset to default
3

Index on computed (AKA virtual) column

Demo

with t(i) as (select 0 union all select i+1 from t where i<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 & 4) as bit))
create index MyTable_ix_flag_bit_2 on MyTable (flag_bit_2)

select count(*) from MyTable where flag_bit_2 = 1

enter image description here

Improve this answer
5
  • Same as just adding new columns & indexes for every bit, yes?
    – GWR
    Dec 8, 2016 at 13:00
  • No. New columns you have to populate.
    – David דודו Markovitz
    Dec 8, 2016 at 13:06
  • Did a few tests, and it seems that when you select anything other than count(*) it doesn't use the index on the virtual column. Added sample to the original question to illustrate.
    – GWR
    Dec 8, 2016 at 13:43
  • 1
    It's a matter of data demographics. If a large chunk of the rows qualifies the condition, there is no point using the index.
    – David דודו Markovitz
    Dec 8, 2016 at 13:50
  • This doesn't make sense for a BIGINT field, not going to create 64 indexes. So it appears there isn't a bitfield type index.
    – Brain2000
    May 2, 2022 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.