|
|
|
|
|
/* Case 8: @VAR <> LITERAL */
declare @i int = null;
set ansi_nulls off;
if @i <> 1
select 'True' as [Case 8, ansi_nulls OFF]
else
select 'FALSE' as [Case 8, ansi_nulls OFF];
set ansi_nulls on;
if @i <> 1
select 'True' as [Case 8, ansi_nulls ON]
else
select 'FALSE' as [Case 8, ansi_nulls ON];
if isnull(@i, 0) <> 1
select 'True' as [Case 8, ansi_nulls ON, solution 1]
else
select 'FALSE' as [Case 8, ansi_nulls ON, solution 1];
if (@i <> 1)
or (@i is null)
select 'True' as [Case 8, ansi_nulls ON, solution 2]
else
select 'FALSE' as [Case 8, ansi_nulls ON, solution 2];
if not exists (select @i intersect select 1)
select 'True' as [Case 8, ansi_nulls ON, solution 3]
else
select 'FALSE' as [Case 8, ansi_nulls ON, solution 3];
go
/* Case 9: @VAR <> @VAR */
declare @i int = null, @j int = 1;
set ansi_nulls off;
if @i <> @j
select 'True' as [Case 9, ansi_nulls OFF]
else
select 'FALSE' as [Case 9, ansi_nulls OFF];
set ansi_nulls on;
if @i <> @j
select 'True' as [Case 9, ansi_nulls ON]
else
select 'FALSE' as [Case 9, ansi_nulls ON];
if (@i <> @j)
or (@i is null and @j is not null)
or (@i is not null and @j is null)
select 'True' as [Case 9, ansi_nulls ON, solution 1]
else
select 'FALSE' as [Case 9, ansi_nulls ON, solution 1];
if not exists (select @i intersect select @j)
select 'True' as [Case 9, ansi_nulls ON, solution 2]
else
select 'FALSE' as [Case 9, ansi_nulls ON, solution 2];
go
/* Case 10: @VAR <> COLUMN */
/* Note that a 1 in the table and a null in the variable also triggers the difference in behavior */
create table T (x int not null, j int null);
insert into T values (1, null);
go
declare @i int = 1;
set ansi_nulls off;
if @i <> (select j from T where x = 1)
select 'True' as [Case 10, ansi_nulls OFF]
else
select 'FALSE' as [Case 10, ansi_nulls OFF];
set ansi_nulls on;
if @i <> (select j from T where x = 1)
select 'True' as [Case 10, ansi_nulls ON]
else
select 'FALSE' as [Case 10, ansi_nulls ON];
/* Solution that can cause bad performance */
if (@i <> (select j from T where x = 1))
or (@i is null and (select j from T where x = 1) is not null)
or (@i is not null and (select j from T where x = 1) is null)
select 'True' as [Case 10, ansi_nulls ON, solution 1]
else
select 'FALSE' as [Case 10, ansi_nulls ON, solution 1];
/* Preferred solution */
if not exists (select @i intersect (select j from T where x = 1))
select 'True' as [Case 10, ansi_nulls ON, solution 2]
else
select 'FALSE' as [Case 10, ansi_nulls ON, solution 2];
go
drop Table T;
go
/* Case 11: COLUMN NOT IN (SET OF LITERALS) */
create table T (x int not null, j varchar(15) null);
insert into T values (1, null), (2, ''), (3, 'Hello world');
go
set ansi_nulls off;
select [case] = 'Case 11, ansi_nulls OFF', * from T where j not in ('', null);
set ansi_nulls on;
select [case] = 'Case 11, ansi_nulls ON', * from T where j not in ('', null);
select [case] = 'Case 11, ansi_nulls ON, solution', * from T where j != '' and j is not null;
drop Table T;
go
/* Case 12 and 13: @VAR NOT IN (SET OF LITERALS)/NOT (@VAR IN (SET OF LITERALS)) */
declare @A int = NULL;
set ANSI_NULLS OFF;
if @A not in (1, 2) print 'case 12, ANSI_NULLS = OFF: true' else print 'case 12, ANSI_NULLS = OFF: FALSE';
set ANSI_NULLS ON;
if @A not in (1, 2) print 'case 12, ANSI_NULLS = ON: true' else print 'case 12, ANSI_NULLS = ON: FALSE';
if @A not in (1, 2) or @A is null print 'case 12, Solve: true' else print 'case 12, Solve: FALSE';
print '';
set ANSI_NULLS OFF;
if not (@A in (1, 2)) print 'case 13, ANSI_NULLS = OFF: true' else print 'case 13, ANSI_NULLS = OFF: FALSE';
set ANSI_NULLS ON;
if not (@A in (1, 2)) print 'case 13, ANSI_NULLS = ON: true' else print 'case 13, ANSI_NULLS = ON: FALSE';
if not (@A in (1, 2)) or @A is null print 'case 13, Solve: true' else print 'case 13, Solve: FALSE';
go
/* Case 14 & 15: COLUMN NOT IN (SET OF LITERALS)/NOT (COLUMN IN (SET OF LITERALS)) */
create table T (i int null)
go
insert into T values (1), (2), (null), (3)
go
declare @A int = 7;
set ansi_nulls off
if @A not in (select i from T) print 'case 14, ANSI_NULLS = OFF: true' else print 'case 14, ANSI_NULLS = OFF: FALSE'
set ansi_nulls on
if @A not in (select i from T) print 'case 14, ANSI_NULLS = ON: true' else print 'case 14, ANSI_NULLS = ON: FALSE'
if not exists (select @A intersect (select i from T)) print 'case 14, Solve: true' else print 'case 14, Solve: FALSE'
print '';
set ANSI_NULLS OFF;
if not (@A in (select i from T)) print 'case 15, ANSI_NULLS = OFF: true' else print 'case 15, ANSI_NULLS = OFF: FALSE';
set ANSI_NULLS ON;
if not (@A in (select i from T)) print 'case 15, ANSI_NULLS = ON: true' else print 'case 15, ANSI_NULLS = ON: FALSE';
if not exists (select @A intersect (select i from T)) print 'case 15, Solve: true' else print 'case 15, Solve: FALSE'
drop table T
go