SA0162 : Column created with option ANSI_PADDING set to OFF

Incorrect setting for ANSI_PADDING can lead to unexpected query behavior and compatibility issues.

Description

When using SQL Server, developers may face issues with how the ANSI_PADDING option affects the behavior of certain data types such as varchar, binary, varbinary, and char. This configuration controls how trailing blanks in character values and zeroes in binary values are handled. Developers should be aware that in future versions of SQL Server, ANSI_PADDING will default to being ON, and any settings explicitly turning it OFF will lead to errors.

Problematic scenario:

SQL
1SET ANSI_PADDING OFF;
2CREATE TABLE SampleTable (Col1 VARCHAR(10));

This command sets ANSI_PADDING to OFF, which can lead to compatibility issues since future SQL Server versions will not support this setting. Trailing spaces are not padded, which can affect data consistency and predictability.

  • May lead to errors or unexpected behaviors when upgrading to future SQL Server versions.

  • Impacts how data is stored and retrieved, which can cause discrepancies in application logic relying on consistent data handling.

How to fix

Ensure compatibility with future SQL Server versions by properly configuring the ANSI_PADDING option.

Follow these steps to address the issue:

  1. Avoid setting ANSI_PADDING to OFF in your SQL Server scripts and tables. Ensure it is set to ON or not specified to rely on the future default system behavior.

  2. Review existing database scripts or table creation statements to identify and update instances where ANSI_PADDING is set to OFF. Modify them to set the option to ON.

  3. Test your database applications to ensure that they operate correctly with ANSI_PADDING set to ON, especially if they handle trailing spaces in character data or zeros in binary data.

Corrected scenario:

SQL
1SET ANSI_PADDING ON;
2CREATE TABLE SampleTable (Col1 VARCHAR(10));

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Rule has no parameters.

Remarks

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

Effort To Fix
13 minutes per issue.
Categories

Design Rules, Bugs

Additional Information
See Also

Other Resources