My understanding is that bitwise operations do not utilize indexes which limits their effectiveness.

Run the following code and the bitwise operation requires many more reads than the = operation (yes I do realise that they are not doing the same thing).

create table #t (id int identity, flags int default 0)

set statistics io off

set nocount on

declare @i int = 0

while @i < 1000000 begin

insert into #t (flags) select @i % 4096

set @i += 1

if @i % 100000 = 0 begin

raiserror ('Inserted %d', 16, 1, @i) with nowait

end

end

set statistics io on

--2,000 reads

select count(*) from #t where flags = 1024

--2,000 reads

select count(*) from #t where flags & 1024 = 1024

--create an index

create index i_t on #t (flags)

--4 reads

select count(*) from #t where flags = 1024

--2,000 reads

select count(*) from #t where flags & 1024 = 1024

drop table #t