SA0163B : Setting ANSI_PADDING to OFF is deprecated

Introduction

The problem is the potential for compatibility issues due to future changes in SQL Server behavior related to the ANSI_PADDING setting.

Description

In T-SQL code and SQL Server environments, the ANSI_PADDING setting controls the treatment of trailing spaces in char and binary columns during data entry. Currently, developers have the option to set this feature to OFF . However, in future versions, SQL Server will default this option to ON unconditionally. This change means any attempt to explicitly set ANSI_PADDING to OFF will result in an error, potentially disrupting existing applications.

For example:

-- Example of problematic query that explicitly sets ANSI_PADDING to OFF
SET ANSI_PADDING OFF;
SELECT * FROM Users;

This example is problematic because setting ANSI_PADDING to OFF will soon trigger an error. Applications dependent on this behavior will fail in upcoming SQL Server releases, necessitating code revisions to ensure compatibility.

Future-proofing code requires removing explicit ANSI_PADDING OFF statements.
Understanding and adapting to SQL Server’s handling of trailing spaces is crucial to avoid data integrity issues.

How to fix

To prevent compatibility issues in future SQL Server versions due to changes in the ANSI_PADDING setting, it’s essential to remove any SET ANSI_PADDING OFF statements from your code.

Follow these steps to address the issue:

1.Identify and locate any instances in your code where SET ANSI_PADDING OFF is being used.

2.Remove the SET ANSI_PADDING OFF statements from your T-SQL scripts.

3.Ensure your applications and T-SQL scripts function correctly with ANSI_PADDING set to ON . Test for data integrity, particularly focusing on operations involving char and binary columns.

4.Educate your team about the updated SQL Server behavior regarding ANSI_PADDING to avoid reintroducing deprecated behavior.

For example:

-- Corrected query without ANSI_PADDING OFF setting
SELECT * FROM Users;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

SET ANSI_PADDING (Transact-SQL)

Deprecated Database Engine Features

Discontinued Database Engine Functionality

Example Test SQL

ALTER DATABASE TestDb SET ANSI_NULLS OFF

ALTER DATABASE TestDb SET ANSI_PADDING OFF

ALTER DATABASE TestDb SET CONCAT_NULL_YIELDS_NULL OFF

SET CONCAT_NULL_YIELDS_NULL OFF

SET ANSI_PADDING ON

SET CONCAT_NULL_YIELDS_NULL,ANSI_PADDING OFF

SET ANSI_NULLS OFF

SET ANSI_PADDING OFF

SET CONCAT_NULL_YIELDS_NULL OFF

SET ANSI_DEFAULTS OFF

Analysis Results

	Message	Line	Column
1	SA0163B : Setting ANSI_PADDING to OFF is deprecated.	3	26
2	SA0163B : Setting ANSI_PADDING to OFF is deprecated.	11	28
3	SA0163B : Setting ANSI_PADDING to OFF is deprecated.	15	4
4	SA0163B : Setting ANSI_PADDING to OFF is deprecated.	19	4

Analysis Rules