Hugo Kornelis (10/28/2010)
If you use SET ANSI_NULLS OFF, there is no need to rewrite anyway. The ANSI_NULLS affects all comparisons with NULL, not only those in a [NOT] IN expression.
Setting this option wouldn't be enough, because it doesn't affect the comparison of a nullable column with a not-null value.
SET ANSI_NULLS OFF;
GO
SELECT NullableColumn FROM NullOperation WHERE NullableColumn <> '1';
GO
NullableColumn
--------------
0
(1 row(s) affected)
But the comparison of a column with NULL is affected:
SET ANSI_NULLS ON;
GO
SELECT NullableColumn FROM NullOperation WHERE NullableColumn <> NULL;
GO
NullableColumn
--------------
(0 row(s) affected)
SET ANSI_NULLS OFF;
GO
SELECT NullableColumn FROM NullOperation WHERE NullableColumn <> NULL;
GO
NullableColumn
--------------
0
1
(2 row(s) affected)