SET ANSI_NULLS ON/OFF

When SET ANSI_NULLS is ON, any kind of comparisons against a null value evaluate to UNKNOWN. This is the ISO standard. In this case, any comparison against a NULL value must use IS NULL or IS NOT NULL to return TRUE/FALSE value.

When SET ANSI_NULLS is OFF, a NULL value can be compared against another NULL value with usual comparison operator ( '=' or '<>' ).

For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values.

SET ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.

When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is enabled.

-- Create table t1 and insert values.
CREATE TABLE t1 (a INT NULL)
INSERT INTO t1 values (NULL)
INSERT INTO t1 values (0)
INSERT INTO t1 values (1)
GO


-- SET ANSI_NULLS to ON and test.
PRINT 'Testing ANSI_NULLS ON'
SET ANSI_NULLS ON
GO
DECLARE @varname int
SELECT @varname = NULL

-- returns nothing
SELECT * FROM t1 
WHERE a = @varname

-- returns nothing
SELECT * FROM t1 
WHERE a <> @varname

-- returns a row (NULL)
SELECT * FROM t1 
WHERE a IS NULL
GO


-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET ANSI_NULLS OFF'
SET ANSI_NULLS OFF
GO
DECLARE @varname int
SELECT @varname = NULL

-- returns a row (NULL)
SELECT * FROM t1 
WHERE a = @varname

-- returns rows (0,1)
SELECT * FROM t1 
WHERE a <> @varname

-- returns a row (NULL)
SELECT * FROM t1 
WHERE a IS NULL
GO


-- Drop table t1.
DROP TABLE t1

Reference:
http://msdn.microsoft.com/en-us/library/ms188048.aspx