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.

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:
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.

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:
-
Identify and locate any instances in your code where SET ANSI_PADDING OFF is being used.
-
Remove the SET ANSI_PADDING OFF statements from your T-SQL scripts.
-
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.
-
Educate your team about the updated SQL Server behavior regarding ANSI_PADDING to avoid reintroducing deprecated behavior.
For example:
1-- Corrected query without ANSI_PADDING OFF setting 2SELECT * FROM Users;

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.


Design Rules, Bugs


SQL
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 |

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 |
