In T sql for sql server, what is the technical difference between the
comparisons "is" and "="
for example:
set @test = null

print @test is null -> true
print @test = null -> false

In SQL, NULL represents a missing/unknown value. The basic comparison
operators like =, <>, <, >, IN, etc return an UNKNOWN result when NULLs are
compared. IS NULL / IS NOT NULL are special types of comparison for
verifying the presence or absence of NULLs.

Read about NULLs and three-value logic in Books Online:
http://msdn.microsoft.com/libr*ary/e...d_02_8p*wy.asp

--
David Portas
SQL Server MVP
--

Sorry. Broken link. Try:
http://msdn.microsoft.com/library/de...qd_02_8pwy.asp

--
David Portas
SQL Server MVP
--

Read about three valued logic in any intro SQL book. You got
everything wrong:

(@test = NULL) -> UNKNOWN for all values of @test

This makes a big difference in DDL and DML clauses.

There is no "IS" in SQL -- that reserved word is part of several
multi-word operators such as IS NULL, IS NOT NULL, and some that are
not widely implemented yet.