Home

Description

The SQL Server code rule "NULL should not be compared directly" means that when comparing two values, an explicit comparison operator should be used instead of just comparing the values directly. This is because a direct comparison of two NULL values will always return true, which may not be the desired result. Instead, an explicit comparison operator should be used to ensure the comparison is done correctly. For example, instead of writing "IF x = NULL", the code should be written as "IF x IS NULL".

Key Benefits

• No Data Corruption: NULL should not be compared directly as it can lead to data corruption.
• Improved Performance: Comparing NULL values directly can lead to slower query performance.
• Eliminate Unexpected Results: Comparing NULL values directly can lead to unexpected results.

Non-compliant Code Example

SELECT Product.ProductID,  
Product.Name,
Color  
FROM Production.Product  
WHERE ListPrice = NULL;  --Non compliant code (Column is directly compared with NULL)

Compliant Code Example

SELECT Product.ProductID,  
Product.Name,
Color  
FROM Production.Product  
WHERE ListPrice Is NULL;  --Compliant code