My theory was that since the bitwise AND operator tells you whether or not "this bit pattern is a subset of that bit pattern", an index on the INT field should perform well in this type of query:

SELECT

  Field1, Field2

FROM

  MyTable

WHERE

  @BitArg = (@BitArg & MyBitField)

To test this, I ran a couple iterations of the following script:

--This is the setup script:

SET NOCOUNT ON

--

DROP TABLE #Binary

DROP TABLE #Compare

--

CREATE TABLE #Binary (BitField INT NOT NULL)

CREATE TABLE #Compare (FlagField INT NOT NULL)

--

INSERT INTO #Binary VALUES (1)

INSERT INTO #Binary VALUES (512)

INSERT INTO #Binary VALUES (2048)

INSERT INTO #Binary VALUES (7168)

INSERT INTO #Binary VALUES (50176)

--

DECLARE @MAX_LOOP INT

DECLARE @Loop INT

--

SET @MAX_LOOP = 10000

SET @Loop = 1

--

WHILE @Loop < @MAX_LOOP BEGIN

	INSERT INTO #Compare VALUES (@Loop)

	SET @Loop = @Loop + 1

END

PRINT CONVERT(VARCHAR(12), @Loop) + ' records inserted...'

SET NOCOUNT OFF

--Here is the test script:

--CREATE CLUSTERED INDEX ncx_Compare ON #Compare (FlagField)

SET NOCOUNT ON

--

DECLARE @IsBitHere INT, @Printable VARCHAR(200), @CountFinds INT

DECLARE c CURSOR FOR SELECT BitField FROM #Binary

--

OPEN c

FETCH NEXT FROM c INTO @IsBitHere

WHILE @@FETCH_STATUS <> -1 BEGIN

	/*

	SELECT 

		FlagField, 

		FlagField & @IsBitHere as "And", 

		FlagField | @IsBitHere as "Or", 

		FlagField ^ @IsBitHere as "xor" 

	FROM #Compare 

	*/

	SELECT @CountFinds = COUNT(*) FROM #Compare WHERE @IsBitHere = (@IsBitHere & FlagField)

	PRINT 'Found ' + CONVERT(VARCHAR(10), @CountFinds) + ' for bitmask: ' + CONVERT(VARCHAR(10), @IsBitHere)

	FETCH NEXT FROM c INTO @IsBitHere

END

--

CLOSE c

DEALLOCATE c

--

SET NOCOUNT OFF

I ran iterations using no index, a nonclustered index on FlagField, and a clustered index on FlagField. I was surprised to find that SQL Server declined to use the an index on any of the indexed iterations. You might say, well, SQL will match 5000 of the 10000 test records for the first bitmask (0x01), however looking at the execution plans, SQL Server only estimated finding 26 out of 10000 total records, and still did an index scan on both the nonclustered AND clustered indexes. My guess, is that it would be better, of course, if the FlagField part of the SARG was on the left side of the equation, but my feeble mind this morning couldn't figure out how to reverse the Boolean equation in the SARG (@IsBitHere = @IsBitHere & FlagField) to get FlagField on the left of the equation. Perhaps someone out there can do it...

Cheers,

jay