NULL = NULL

One of my friend had discussion with me some days back that NULL is not equal to another NULL...! 

Is that TRUE ?

I said that "NO, IT IS NOT ALWAYS...!"

What that means.. "NOT ALWAYS" ?

In SQL Server, we have SET option called "ANSI_NULLS"

Is there any way to identify that what are all the options have been set to ON ?

DBCC USEROPTIONS
GO

Its a session specific option. So, It will list out What are all the user options have been set to ON along with some other options!

OK.

NULL is NOT EQUAL to another NULL. How ? 

When ANSI_NULLS is set to ON

SET ANSI_NULLS ON
GO

IF (NULL=NULL)
    PRINT 'EQUAL'
ELSE
    PRINT 'NOT EQUAL'
GO

Result : NOT EQUAL

 
NULL is EQUAL to another NULL. How ? 

When ANSI_NULLS is set to OFF

SET ANSI_NULLS OFF
GO

IF (NULL=NULL)
    PRINT 'EQUAL'
ELSE
    PRINT 'NOT EQUAL'
GO

Result : EQUAL

So, NULL is not equal to another NULL - NOT ALWAYS, It depends on ANSI_NULLS option!