SA0163B : Setting ANSI_PADDING to OFF is deprecated

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:

SQL
Copy
1-- Example of problematic query that explicitly sets ANSI_PADDING to OFF
2SET ANSI_PADDING OFF;
3SELECT * 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:

SQL
Copy
1-- Corrected query without ANSI_PADDING OFF setting
2SELECT * 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.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1ALTER DATABASE TestDb SET ANSI_NULLS OFF
 2
 3ALTER DATABASE TestDb SET ANSI_PADDING OFF
 4
 5ALTER DATABASE TestDb SET CONCAT_NULL_YIELDS_NULL OFF
 6
 7SET CONCAT_NULL_YIELDS_NULL OFF
 8
 9SET ANSI_PADDING ON
10
11SET CONCAT_NULL_YIELDS_NULL,ANSI_PADDING OFF
12
13SET ANSI_NULLS OFF
14
15SET ANSI_PADDING OFF
16
17SET CONCAT_NULL_YIELDS_NULL OFF
18
19SET 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
See Also

Other Resources

Analysis Rules