SA0163 : Deprecated setting of database options ANSI_PADDING to OFF

The ANSI_PADDING setting affects how SQL Server handles the space padding between fixed-length and variable-length columns. This configuration can lead to inconsistencies and future compatibility issues.

Improper configuration of certain database options can lead to future compatibility issues. Specifically, when ANSI_PADDING is set to OFF, it could cause problems because upcoming versions of SQL Server will always require ANSI_PADDING to be ON.

Example of a query checking these settings:

1SELECT name, is_ansi_padding
2FROM sys.databases
3WHERE is_ansi_padding = 0;

This query highlights databases with potentially problematic configurations. Maintaining the default settings is important for avoiding unexpected errors and ensuring future compatibility.

If ANSI_PADDING is set to OFF, certain columns could behave unpredictably with regard to trailing spaces, especially under future versions of SQL Server which will require ANSI_PADDING to always be ON.

• The option currently alters data insertion behavior, potentially leading to data retrieval inconsistencies.

• Future SQL Server updates will mandate ANSI_PADDING to be ON, and attempts to set it OFF will result in errors.

Ensure correct configuration of ANSI_PADDING setting to maintain compatibility with future SQL Server versions.

Follow these steps to address the issue:

1. Verify the current settings for ANSI_PADDING using a query. If the settings are OFF, they need to be corrected.

2. Open SQL Server Management Studio (SSMS) and navigate to each affected database.

3. For each database, turn on the required settings by using ALTER DATABASE statements. Set ANSI_PADDING to ON.

Example of updated database settings:

1ALTER DATABASE DatabaseName SET ANSI_PADDING ON;

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