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