Difference between "=" and "is" in sql server

Question

I am having problem while understanding = and is operators in SQL Server.

Consider the following example queries which are having different behaviors in their respective output:

SELECT * FROM tableName WHERE colName IS NULL;
SELECT * FROM tableName WHERE colName = NULL;

First query will provide the required output i.e. select those records for which colName is having a null value. But the second query will result in zero matching records.

Please clarify different uses of these operators with pros and cons.

EDIT

Here, most of the answers are claiming that = doesn't work with null, but the following statement will work with null and =.

SET ANSI_NULLS OFF
SELECT * FROM tableName WHERE colName = NULL;

This will provide the same result as statement having is operator.

Answer 1

Nothing equals null.

Not even null equals null.

null is not a value, it is more like a concept, or a mark, meaning unknown value.

As such, you need two operators for this, one for equality, and one for checking the concept of null.

Once you start to think of null as "unknown value" a lot of the other behavior also makes sense.

10 + null? Add an unknown value to 10? Obviously you will have another unknown value as a result.

For more information, please check the documentation of the equality operator in T-SQL.

Additionally, see the documentation for SET ANSI_NULL.

Note that the documentation is in conflict about the behavior of x = null between the equality operator (documentation says it will always be false if x is non-null) whereas SET ANSI_NULLS documentation says that x = null will behave equivalent to x is null when the option is turned on.