= NULL Should Be IS NULL

The expresion of = NULL in a where clause is not valid because nothing will ever = NULL, NULL doesn’t even evaluate to = NULL in SQL Server.

The correct syntax is to use the IS NULL rather than = NULL.

SELECT OBJECT_NAME(sc.object_id) AS TableName, 
       sc.name AS ColumnName
  FROM sys.columns sc 
 WHERE sc.name = NULL;

Should be written as:

SELECT OBJECT_NAME(sc.object_id) AS TableName, 
       sc.name AS ColumnName
  FROM sys.columns sc 
 WHERE sc.name IS NULL;

Do you agree?

If you don’t agree that = NULL is being considered as technical debt, that is just fine, in the Database Health Reports settings dialog you can turn the check = null in the where clause off. As long as you have coding standards that state one opinion or another on cursors as being your standard, then you are doing better than most. I would strongly recommend turning this one off.

A good coding standard can over-ride many of the items that may be considered as technical debt.

Contact Info

Stedman Solutions, LLC.
PO Box 3175
Ferndale WA 98248

Phone: (360)610-7833

Download this page in PDF format

Do you agree?

See Also:

Contact Info

Leave a Reply Cancel reply