SA0122 : Use ISNULL(Column,Default value) on nullable columns in expressions

Introduction

Nullable columns in expressions can cause unexpected results if not properly handled.

Description

When working with T-SQL in SQL Server, using nullable columns in expressions without checking for IS NULL or using the ISNULL function can lead to unintended outcomes. This is because operations involving nulls can produce null results, which might not be the desired behavior.

For example:

-- Example of a problematic query
SELECT ColumnA + ColumnB FROM TableName;

If ColumnA or ColumnB contains null values, the result of the expression will also be null. This can lead to issues in calculations or aggregations where a null value is not intended. It’s crucial to ensure that nulls are handled explicitly to maintain data integrity and accuracy in results.

Calculations may return null, leading to incomplete data analysis.
Aggregations such as sums or averages might be skewed or incorrect.

How to fix

To resolve issues related to nullable columns in expressions and ensure accurate comparisons and calculations, handle null values explicitly using the ISNULL function.

Follow these steps to address the issue:

1.Identify nullable columns in your queries where expressions or comparisons are performed.

2.Use the ISNULL function to provide a default value for any nullable column in expressions. This ensures that null values are replaced with a value that maintains the integrity of the calculation or comparison.

3.Verify the logic of your queries, ensuring that all expression outputs are properly handled to avoid unintended null results.

For example:

-- Example of corrected query handling nullable columns
SELECT ISNULL(ColumnA, 0) + ISNULL(ColumnB, 0) AS SumValue
FROM TableName;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Name	Description	Default Value
IgnoreNullableColumnToNullableColumnAssignment	Ignore assignment of nullable column to nullable column.	yes
IgnoreNullableColumnInOuterJoin	Ignore nullable columns referenced inside OUTER JOIN ON clause.	yes
IgnoreNullableColumnComparedToConstant	Ignore nullable columns when compared to constant value.	yes
IgnoreNullableForeignKeyColumnComparedToReferencedKeyInJoinClause	Ignore nullabe columns which are FK columns and are compared to the FK referenced key columns in JOIN clause.	yes

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

5 minutes per issue.

Additional Information

ISNULL (Transact-SQL)

Example Test SQL

CREATE PROCEDURE testsp_SA0122
@Size varchar(10)
AS
SELECT Name, Weight, Color, Size
FROM Production.Product
WHERE Color = 'Black' AND
      Size = @Size
ORDER BY Name;

Analysis Results

	Message	Line	Column
1	SA0122 : Column [Production].[Product].[Size] is nullable. Use ISNULL(Column,Default Value) on nullable columns in expressions.	7	6
2	SA0122 : Column [Production].[Product].[Weight] is nullable. Use ISNULL(Column,Default Value) on nullable columns in expressions.	4	13
3	SA0122 : Column [Production].[Product].[Color] is nullable. Use ISNULL(Column,Default Value) on nullable columns in expressions.	4	21
4	SA0122 : Column [Production].[Product].[Size] is nullable. Use ISNULL(Column,Default Value) on nullable columns in expressions.	4	28

Analysis Rules