SA0162 : Column created with option ANSI_PADDING set to OFF

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:

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.

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:

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

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

Rule has no parameters.

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

Design Rules, Bugs

SET ANSI_PADDING (Transact-SQL)

Deprecated Database Engine Features

Discontinued Database Engine Functionality

Other Resources