Home

Description

• The ANSI_NULLS, ANSI_PADDING and CONCAT_NULL_YIELDS_NULL code rules indicate that the SQL Server should not be configured to use these settings. ANSI_NULLS defines how to handle comparisons involving NULL values. When set to ON, two NULL values are considered equal. When set to OFF, two NULL values are considered unequal. ANSI_PADDING defines how to handle trailing spaces in character data. When set to ON, trailing spaces are retained. When set to OFF, trailing spaces are removed. CONCAT_NULL_YIELDS_NULL defines how to handle the concatenation of NULL values. When set to ON, the result of the concatenation is NULL. When set to OFF, the result of the concatenation is an empty string.
• For optimal performance, it is recommended that these settings not be configured as they can lead to unexpected results and can affect the performance of the SQL Server.

Key Benefits

• Affect the Performance: ANSI_NULLS should not be configured as it will affect the performance of the database engine by forcing it to check for null values in every comparison.
• Unexpected Results: ANSI_PADDING should not be configured as it can lead to unexpected results when comparing strings of different lengths.
• Incorrect Results: CONCAT_NULL_YIELDS_NULL should not be configured as it can lead to incorrect results when combining strings with null values.

Non-compliant Code Example

SET ANSI_NULLS OFF  --Non compliant code
Go
SET ANSI_PADDING OFF  --Non compliant code
GO
SET CONCAT_NULL_YIELDS_NULL OFF  --Non compliant code
GO