SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table emp ( 
    emp_no int not null primary key, 
    title varchar(10) not null, 
    salary int not null
    , 
        constraint c1 check (emp_no > 0), 
        constraint c2 check (title in ('BOSS','WORK'))
);

--insert into emp with t (n) as (
--    values (1) 
--    union all 
--    select n+1 from t where n+1 < 1000
--) select n, case when mod(n,10) = 0 then 'BOSS' else 'WORK' end, 
--            case when mod(n,10) = 0 then 110 else 0 end + mod(n,90) 
--from t;

alter table emp add constraint cc1 
    CHECK ( (title <> 'BOSS' OR salary > 100) );


alter table emp add constraint cc2
    CHECK ( (title <> 'WORK' OR salary <= 100) );
SET SHOWPLAN_XML ON;  
select * 
from emp 
where title = 'BOSS' and salary <= 100;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="select * &#xa;from emp &#xa;where title = &apos;BOSS&apos; and salary &lt;= 100" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.0032831" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x38C842F55E7A3C11" QueryPlanHash="0x2D25D9B0E3CBC2E7" CardinalityEstimationModelVersion="150" ParameterizedText="(@1 varchar(8000),@2 tinyint)SELECT * FROM [emp] WHERE [title]=@1 AND [salary]&lt;=@2"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="144"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1144184"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="24" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="emp_no"></ColumnReference><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="title"></ColumnReference><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="salary"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="emp_no"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="title"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="salary"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Index="[PK__emp__129850FA0E29F718]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_b3762f6596f648c68620ad3399000fe6].[dbo].[emp].[salary]&lt;=CONVERT_IMPLICIT(int,[@2],0) AND [fiddle_b3762f6596f648c68620ad3399000fe6].[dbo].[emp].[title]=[@1]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="salary"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1002"><ScalarOperator><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@2"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="title"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@1"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp><ParameterList><ColumnReference Column="@2" ParameterDataType="tinyint" ParameterCompiledValue="(100)"></ColumnReference><ColumnReference Column="@1" ParameterDataType="varchar(8000)" ParameterCompiledValue="&apos;BOSS&apos;"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


select * 
from emp 
where title = 'BOSS' and salary <= 100 and 1=0


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="select * &#xa;from emp &#xa;where title = &apos;BOSS&apos; and salary &lt;= 100 and 1=0" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="1.157e-06" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x7CE616D07172DD06" QueryPlanHash="0x7E7FC277F0BBE769" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="112"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1144184"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-06" AvgRowSize="24" EstimatedTotalSubtreeCost="1.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="emp_no"></ColumnReference><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="title"></ColumnReference><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="salary"></ColumnReference></OutputList><ConstantScan></ConstantScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


select * 
from emp 
where title = 'BOSS' and salary <= 100 and ( (title <> 'BOSS' OR salary > 100) )


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="select * &#xa;from emp &#xa;where title = &apos;BOSS&apos; and salary &lt;= 100 and ( (title &lt;&gt; &apos;BOSS&apos; OR salary &gt; 100) )" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.0032831" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x3E24A0CBA067B8CE" QueryPlanHash="0x2D25D9B0E3CBC2E7" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="184"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1144184"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="24" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="emp_no"></ColumnReference><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="title"></ColumnReference><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="salary"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="emp_no"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="title"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="salary"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Index="[PK__emp__129850FA0E29F718]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_b3762f6596f648c68620ad3399000fe6].[dbo].[emp].[salary]&lt;=(100) AND [fiddle_b3762f6596f648c68620ad3399000fe6].[dbo].[emp].[title]=&apos;BOSS&apos; AND ([fiddle_b3762f6596f648c68620ad3399000fe6].[dbo].[emp].[title]=&apos;WORK&apos; OR [fiddle_b3762f6596f648c68620ad3399000fe6].[dbo].[emp].[salary]&gt;(100))"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="salary"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(100)"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="title"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;BOSS&apos;"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Logical Operation="OR"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="title"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;WORK&apos;"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="salary"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(100)"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


select * 
from emp 
where title = 'BOSS' and salary <= 100 and ( NOT (title = 'BOSS' AND salary <= 100) )


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="select * &#xa;from emp &#xa;where title = &apos;BOSS&apos; and salary &lt;= 100 and ( NOT (title = &apos;BOSS&apos; AND salary &lt;= 100) )" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.0032831" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x3E24A0CBA067B8CE" QueryPlanHash="0x2D25D9B0E3CBC2E7" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150" ParameterizedText="(@1 varchar(8000),@2 tinyint,@3 varchar(8000),@4 tinyint)SELECT * FROM [emp] WHERE [title]=@1 AND [salary]&lt;=@2 AND NOT ([title]=@3 AND [salary]&lt;=@4)"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="216"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1144184"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="24" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="emp_no"></ColumnReference><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="title"></ColumnReference><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="salary"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="emp_no"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="title"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="salary"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Index="[PK__emp__129850FA0E29F718]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_b3762f6596f648c68620ad3399000fe6].[dbo].[emp].[salary]&lt;=(100) AND [fiddle_b3762f6596f648c68620ad3399000fe6].[dbo].[emp].[title]=&apos;BOSS&apos; AND ([fiddle_b3762f6596f648c68620ad3399000fe6].[dbo].[emp].[title]&lt;&gt;&apos;BOSS&apos; OR [fiddle_b3762f6596f648c68620ad3399000fe6].[dbo].[emp].[salary]&gt;(100))"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="salary"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(100)"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="title"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;BOSS&apos;"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Logical Operation="OR"><ScalarOperator><Compare CompareOp="NE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="title"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;BOSS&apos;"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_b3762f6596f648c68620ad3399000fe6]" Schema="[dbo]" Table="[emp]" Column="salary"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(100)"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp><ParameterList><ColumnReference Column="@4" ParameterDataType="tinyint" ParameterCompiledValue="(100)"></ColumnReference><ColumnReference Column="@3" ParameterDataType="varchar(8000)" ParameterCompiledValue="&apos;BOSS&apos;"></ColumnReference><ColumnReference Column="@2" ParameterDataType="tinyint" ParameterCompiledValue="(100)"></ColumnReference><ColumnReference Column="@1" ParameterDataType="varchar(8000)" ParameterCompiledValue="&apos;BOSS&apos;"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>