bitwise operator and index usage

Hi all,

we have a index on segment_0 column  but wanted to check if sql server will use that index efficiently because of the use of bitwise operator ?  please see the query below .

SELECTCOUNT(MemberID)FROMdbo.MemberSegmentWHERESiteID = 23622320151 AND Segment_0 = Segment_0|CAST(2048 ASBIGINT)

OPTION (MAXDOP 8) 

<u5:p> thanks</u5:p>

siddharth<u5:p></u5:p>

August 25th, 2015 9:01pm

A bitwise operator is like any other expression. This expression is not sargable because the operator needs to be applied to the column value before the result is known. 

If you have a nonclustered index with SiteID as the leftmost key column followed by SegMent_0 and MemberId is an included column (or SiteID is the leftmost clustered index key), then only the rows with the matching SiteID will be touched.  But the SiteID of every matching row will need to be evaluated.  Generally speaking, bit maps violate normalization rulues (not atomic) and are difficult to optimize.  Consider separate columns (including separate computed columns).

Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 10:24pm

thanks Dan..

is there a lot of improvement on performance of "bitwise operator + index usage "  on sql 2012?

if that's the case , can you point me to some documentation on this pls?

thanks

siddharth

August 26th, 2015 10:16am

I'm not aware of any improvements in SQL 2012 or later versions that would allow a bitmap operator applied to a column to be sargable.

I assume each of bits segment_0 has a discrete meaning.  If you don't want to create a separate column for each, you could create a computed column for those often used a WHERE clause.  Below is a computed column example:

ALTER TABLE dbo.MemberSegment
	ADD Segment_0_2048 AS Segment_0 | CAST(2048 AS BIGINT);

CREATE INDEX index1 ON dbo.MemberSegment(SiteID, Segment_0_2048) INCLUDE(MemberID);

--this will use and index seek of the covering index
SELECT
	COUNT(MemberID)
FROM dbo.MemberSegment
WHERE SiteID = 23622320151 
AND Segment_0_2048 = 2048;

Free Windows Admin Tool Kit Click here and download it now
August 26th, 2015 10:08pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics