Home Dashboard Directory Help
Search

Convert Inequality Predicates on Bit / Boolean Columns to Equality Predicate by Michael Ferguson


Status: 

Active


2
0
Sign in
to vote
Type: Suggestion
ID: 812278
Opened: 12/24/2013 9:14:25 AM
Access Restriction: Public
1
Workaround(s)
view

Description

SQL 2012 SP1 doesn't seem to use an inequality predicate on a bit field as a search argument ("SARG"). Clearly, since bit fields may only contain two values, an inequality predicate for a bit can easily be converted to an equality predicate. Converting "<> 0" to "= 1" (and "<> 1" to "= 0") should be a relatively easy optimization to add to the algebrizer / optimizer. Although savvy users can easily fix explicit SQL code by rewriting the inequality predicate as an equality predicate, this optimization will benefit certain ORM frameworks that are not smart enough to evaluate "! obj.BitColumn" in code to "BitColumn = 0" (and instead produce code like "obj.BitColumn <> 1", which will not SARG).
Details
Sign in to post a comment.
Sign in to post a workaround.
Posted by Martin Smith on 12/24/2013 at 4:39 PM
A workaround is to add a redundant check constraint. Example here http://dba.stackexchange.com/q/31148/3690
File Name Submitted By Submitted On File Size  
SQL Bit SARG Inequality Test.sql 12/24/2013 1 KB