 |
|
SQL Server Tips by Gama and Naughter
|
About NULL in TSQL
Some programming languages use the = operator to compare expressions
with a NULL value. This is legal in TSQL only when the SET option
ANSI_NULLS is OFF, but it is ON by default. This happens because of
the compliance with the SQL-92 standard, which requires that a
comparison against a NULL value always evaluates to FALSE (even if
both expressions are NULL). It is important to be aware of how NULL
behaves in TSQL when using XP’s because: variables in TSQL have NULL
as a default value. This must be taken into consideration when
reading parameters values from variables. XP’s might return NULL to
a parameter variable that would be used afterwards in a TSQL
statement. Unexpected results will occur if the NULL value is not
handled properly.
This is an example of using ANSI_NULLS:
declare @b int
SET @b=1
SET ANSI_NULLS ON
if @b <> NULL PRINT 1
if @b != NULL PRINT 2
SET @b=NULL
if @b = NULL PRINT 3
SET ANSI_NULLS OFF
SET @b=1
if @b <> NULL PRINT 4
if @b != NULL PRINT 5
SET @b=NULL
if @b = NULL PRINT 6
The output will be:
4
5
6
It is recommended to use the IS operator because it is SQL-92
compliant, making the code more portable. SET ANSI_NULLS permissions
affect all users and this is an issue because some users might want
to develop SQL-92 compliant code.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
