SA0163 : Deprecated setting of database options ANSI_PADDING to OFF

Introduction

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

Description

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:

SELECT name, is_ansi_padding
FROM sys.databases
WHERE 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.

How to fix

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:

  ALTER DATABASE DatabaseName SET ANSI_PADDING ON;

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.

Additional Information

SET ANSI_PADDING (Transact-SQL)

Deprecated Database Engine Features

Discontinued Database Engine Functionality

Analysis Rules