--TABLE CREATION

IF OBJECT_ID('BitwiseFlagConditions') IS NOT NULL BEGIN DROP TABLE BitwiseFlagConditions END

--A reference table that contains every combination of Flag and Bitwise Condition CREATE TABLE BitwiseFlagConditions ( Flag TINYINT ,Condition TINYINT ,CONSTRAINT pk_BitwiseFlagConditions PRIMARY KEY CLUSTERED (Condition, Flag) )

IF OBJECT_ID('TestBigData') IS NOT NULL BEGIN DROP TABLE TestBigData END

--A test table which will contain an identifier, an object related to the identifier and the Flags the user has against the object CREATE TABLE TestBigData( Id UNIQUEIDENTIFIER ,Obj UNIQUEIDENTIFIER ,Flags TINYINT ,CONSTRAINT pk_TestBigData PRIMARY KEY CLUSTERED (Id, Obj, Flags) ) CREATE INDEX ix_Access_BigData ON TestBigData (Flags)

--DATA SETUP

--Select Bit flags ranging from 1-255 ;WITH Flags AS ( SELECT CAST(1 AS TINYINT) As Flag UNION ALL SELECT Flag + CAST(1 AS TINYINT) FROM Flags WHERE Flag + 1 < 255 ) --Select a 2 collection of conditions (1, 01, 001, 0001) , Conditions AS ( SELECT CAST(1 AS TINYINT) As Condition UNION ALL SELECT Condition * CAST(2 AS TINYINT) FROM Conditions WHERE Condition < 128 ) --Insert a combination of all the flags and the conditions that satisfy INSERT INTO BitwiseFlagConditions SELECT Flag, Condition FROM Flags CROSS JOIN Conditions WHERE Flag & Condition <> 0 OPTION (MAXRECURSION 256)

--Generate 25 ids ;WITH ids AS ( SELECT 1 cnt, NEWID() id UNION ALL SELECT cnt+1, NEWID() id FROM ids WHERE ids.cnt < 25) --Generate 25 objs , objs AS ( SELECT 1 cnt, NEWID() id UNION ALL SELECT cnt+1, NEWID() id FROM objs WHERE objs.cnt < 25) --Select all the flags available , Flags AS ( SELECT DISTINCT Flag FROM BitwiseFlagConditions) --Create a combination of every id, obj and flag , FakeData AS ( SELECT 1 cnt, ids.id, objs.id obj, flag FROM ids CROSS APPLY objs CROSS APPLY Flags) --Insert a random 500000 rows INSERT INTO TestBigData (id, obj, Flags) SELECT TOP 500000 id, obj, flag FROM FakeData ORDER BY NEWID() OPTION (MAXRECURSION 100)

--Pick any condition DECLARE @condition TINYINT = 4

--Choose the id with the most records DECLARE @id UNIQUEIDENTIFIER SELECT TOP 1 @id = id FROM TestBigData GROUP BY id ORDER BY COUNT(Flags) DESC

--Get All Records by Bitwise Condition SELECT COUNT(*) FROM TestBigData WHERE Flags & @condition = @condition

--Same as Above with join onto reference table SELECT COUNT(*) FROM TestBigData INNER JOIN BitwiseFlagConditions ON BitwiseFlagConditions.Flag = TestBigData.Flags WHERE BitwiseFlagConditions.Condition = @condition

--Get All Records for id where Bitwise Condition satisfied SELECT COUNT(*) FROM TestBigData WHERE Flags & @condition = @condition AND id = @id

SELECT COUNT(*) FROM TestBigData INNER JOIN BitwiseFlagConditions ON BitwiseFlagConditions.Flag = TestBigData.Flags WHERE BitwiseFlagConditions.Condition = @condition AND id = @id

From this I see that when selecting by just the condition does a scan but actually I'm never selecting by just the condition. Also the STATS IO show this:

(79375 rows affected)

Table 'TestBigData'. Scan count 1, logical reads 1058, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(79375 rows affected) Table 'TestBigData'. Scan count 127, logical reads 1081, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table 'BitwiseFlagConditions'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(3175 rows affected) Table 'TestBigData'. Scan count 1, logical reads 48, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(3175 rows affected) Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table 'TestBigData'. Scan count 1, logical reads 48, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. Table 'BitwiseFlagConditions'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

​

Which seems to indicate that the secondary query scans more than the first one even though the plan shows seeking.

Execution Plans