为什么不要创建将ANSI_PADDING设置为OFF的列？

SQL Prompt是一款实用的SQL语法提示工具。它根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。本文介绍了不要创建将ANSI_PADDING设置为OFF的列。

查找永久更改表中某些数据类型检索方式的连接设置，这有点奇怪，但是如果在创建临时或永久表时使用SET ANSI_PADDING OFF，就会发生这种情况。在创建表时，由于设置不当，或者偶然使用带有DBLib连接的旧应用程序，这会导致表中的某些列从那时起奇怪地或不一致地处理某些字符串或二进制数据类型的尾随空格。

此选项已被弃用，在某些时候，它将被删除（它将始终为“on”）。SQL Prompt具有不赞成使用的语法规则DEP013，它将警告您使用此选项以及其他不赞成使用的SET选项。

什么是ANSI填充？为什么？

在SQL的早期，如何处理字符串的问题引起了争议。定义和固定字符串长度的CHAR数据类型旨在使数据检索简单而有效。字符串以指定的长度存储在CHAR数据类型中。对于较短的字符串，数据类型中所有剩余的字符位置都用空格（CHAR）或零（BINARY）填充。这些填充空格是字符串的一部分吗？如果不是，您如何区分故意填充空格的值和自动填充的值？

SQL的早期实现通常在检索数据时修剪掉CHAR中的所有尾随空格，除非该列是NOT NULL。但是，很明显，为了符合ANSI SQL标准，必须对此进行更改。NIST测试套件检查是否始终填充CHAR数据类型，并且对于CHAR或VARCHAR数据类型，用户输入的尾随空格都不会被截断。SQL Server决定，为了遵守规则，将随数据一起检索任何尾随空格（无论是故意还是作为填充自动添加的），对于二进制数据类型的尾随零也是如此。但是，由于在旧的体制下编写了太多代码，因此Transact-SQL中引入了一种称为ANSI_PADDING的设置。当它关闭时，它允许此旧代码照常工作。似乎每个人都很高兴。

一旦有关CHAR数据类型的ANSI-ISO标准争议平息下来，就会引入新的数据类型和新的表类型。ANSI_PADDING争议仅影响当时存在的类型，而用户为此目的定义的长度。现在可以将字符串存储为NVARCHAR、VARCHAR、NCHAR或CHAR的定义大小。二进制数据可以存储为BINARY或VARBINARY的定义大小。对于CHAR（n）、BINARY（n）、VARCHAR（n）或VARBINARY（n）的较早数据类型，在创建表时ANSI_PADDING选项的设置会影响SQL Server随后处理这些字符串的方式。

但是，后来的NCHAR、NVARCHAR、NTEXT、TEXT或IMAGE数据类型并非如此。未定义长度的类型VARBINARY（MAX）、VARCHAR（MAX）和NVARCHAR（MAX）也不受影响。

旧数据库开发人员真正需要关闭ANSI填充的唯一用途是无需使用该RTRIM()功能即可进行字符串连接。避免必须使用RTRIM()函数似乎是个好主意，但是填充规则的行为与有可空列的行为不一致。另外，随着新类型的表的引入，没有人愿意使它们向后兼容，因此适用的规则通常在ANSI_PADDING关闭时对表变量根本不起作用。同样，如果您尝试在计算列或索引视图上创建或更改索引，则很可能会陷入困境。如果您将ANSI_PADDING设置为OFF，则根本不允许这样做。

那么，规则是什么？

ANSI标准的简单行为是，对于固定宽度类型插入的数据，总是用尾随空格或零填充到指定长度，然后，对于所有数据类型，任何尾随空格或零都被视为数据的一部分，依此类推。当SQL Server将数据检索到内存时，将永远不会修剪它们。

如果在创建表和列时将ANSI_PADDING切换为OFF，则行为将变得更加复杂。幸运的是，是否存在尾随空格不会影响WHERE子句中的字符串比较，因为无论设置什么，这些始终会忽略它们。它也不会对比较产生很大的影响。关闭ANSI_PADDING的主要效果如下：

• CHAR NOT NULL和BINARY NOT NULL列在插入数据时被填充，并且随后未进行修剪（与ANSI标准相同的行为）

• 在检索时会修剪可空的CHAR和BINARY列（因此，当然在插入时不再填充）。您会丢失任何尾随空格或故意添加的零

• 检索时会修剪VARBINARY和VARCHAR列，因此您会丢失任何尾随空格或故意添加的零

如果您需要说服力，我们可以证明所有这些。

PRINT 'Creating a temporary table with ANSI_PADDING ON';
SET ANSI_PADDING ON;
SET NOCOUNT ON;
CREATE TABLE #OnAnsiPaddingTest
  (
  TenCharsNull CHAR(10) NULL,
  TenCharsNotNull CHAR(10) NOT NULL,
  TenVarcharNull VARCHAR(10) NULL,
  TenVarcharNotNull VARCHAR(10) NOT NULL,
  TenVarbinaryNull VARBINARY(10) NULL,
  TenVarbinaryNotNull VARBINARY(10) NOT NULL
  );
<a id="post-1115931-_Hlk33089691"></a>PRINT 'Now creating identical temp table with ANSI_PADDING OFF';
SET ANSI_PADDING OFF;
SET NOCOUNT ON;
CREATE TABLE #OffAnsiPaddingTest
  (
  TenCharsNull CHAR(10) NULL,
  TenCharsNotNull CHAR(10) NOT NULL,
  TenVarcharNull VARCHAR(10) NULL,
  TenVarcharNotNull VARCHAR(10) NOT NULL,
  TenVarbinaryNull VARBINARY(10) NULL,
  TenVarbinaryNotNull VARBINARY(10) NOT NULL
  );
PRINT 'Now creating identical table variable with ANSI_PADDING OFF';
DECLARE @OffAnsiPaddingTest table
  (
  TenCharsNull CHAR(10) NULL,
  TenCharsNotNull CHAR(10) NOT NULL,
  TenVarcharNull VARCHAR(10) NULL,
  TenVarcharNotNull VARCHAR(10) NOT NULL,
  TenVarbinaryNull VARBINARY(10) NULL,
  TenVarbinaryNotNull VARBINARY(10) NOT NULL
  );
PRINT 'Switching ANSI_PADDING back on'
SET ANSI_PADDING ON;
PRINT 'inserting into both tables'
INSERT INTO #OffAnsiPaddingTest
  (TenCharsNull, TenCharsNotNull, TenVarcharNull, TenVarcharNotNull,
TenVarbinaryNull, TenVarbinaryNotNull)
VALUES
  ('First      ', 'Second    ', 'Third     ', 'fourth    ', 0x1234560000,
0x1234560000), --padded to 10
  ('First', 'Second', 'Third', 'fourth', 0x123456, 0x123456); --no trailing padding
INSERT INTO #OnAnsiPaddingTest
  (TenCharsNull, TenCharsNotNull, TenVarcharNull, TenVarcharNotNull,
TenVarbinaryNull, TenVarbinaryNotNull)
VALUES
  ('First      ', 'Second    ', 'Third     ', 'fourth    ', 0x1234560000,
0x1234560000), --padded to 10
  ('First', 'Second', 'Third', 'fourth', 0x123456, 0x123456); --no trailing padding
INSERT INTO @OffAnsiPaddingTest
  (TenCharsNull, TenCharsNotNull, TenVarcharNull, TenVarcharNotNull,
TenVarbinaryNull, TenVarbinaryNotNull)
VALUES
  ('First      ', 'Second    ', 'Third     ', 'fourth    ', 0x1234560000,
0x1234560000), --padded to 10
  ('First', 'Second', 'Third', 'fourth', 0x123456, 0x123456); --no trailing padding
PRINT 'Selecting from first table, created with ANSI padding ON (<> shows extent of string)'
SELECT '<' + Coalesce(TenCharsNull, '') + '> <' + TenCharsNotNull + '> <'
       + Coalesce(TenVarcharNull, '') + '> <' + TenVarcharNotNull + '> <'
       + Coalesce(Convert(VARCHAR(MAX), TenVarbinaryNull, 2), 'null') + '> <'
       + Convert(VARCHAR(MAX), TenVarbinaryNotNull, 2) + '>' AS AnsiPaddingOn
  FROM #OnAnsiPaddingTest AS APT;
PRINT 'Selecting from second table, created with ANSI padding OFF: Same query'
SELECT '<' + Coalesce(TenCharsNull, 'null') + '> <' + TenCharsNotNull + '> <'
       + Coalesce(TenVarcharNull, 'null') + '> <' + TenVarcharNotNull + '> <'
       + Coalesce(Convert(VARCHAR(MAX), TenVarbinaryNull, 2), 'null') + '> <'
       + Convert(VARCHAR(MAX), TenVarbinaryNotNull, 2) + '>' AS AnsiPaddingOff
  FROM #OffAnsiPaddingTest AS APT;
PRINT 'Selecting from table variable, created with ANSI padding OFF: Same query'
SELECT '<' + Coalesce(TenCharsNull, 'null') + '> <' + TenCharsNotNull + '> <'
       + Coalesce(TenVarcharNull, 'null') + '> <' + TenVarcharNotNull + '> <'
       + Coalesce(Convert(VARCHAR(MAX), TenVarbinaryNull, 2), 'null') + '> <'
       + Convert(VARCHAR(MAX), TenVarbinaryNotNull, 2) + '>' AS AnsiPaddingOff
  FROM @OffAnsiPaddingTest AS APT;
  DROP TABLE #OnAnsiPaddingTest;
DROP TABLE #OffAnsiPaddingTest;

您会看到以下消息：

使用ANSI_PADDING ON创建一个临时表
现在使用ANSI_PADDING OFF创建相同的临时表
现在使用ANSI_PADDING OFF'创建相同的表变量
重新打开ANSI_PADDING
插入两个表
从第一个表中选择，并在ANSI填充为ON的情况下创建（<>显示字符串的范围）
从第二个表中选择，使用ANSI填充OFF创建：同一查询
从表变量中选择，使用ANSI填充OFF创建：相同的查询

结果是这样的：

正确的。与往常一样，在创建表时将ANSI_PADDING设置为ON，我们故意添加尾随空格或零的第一行就不会被裁剪。没有尾随空格的第二行被一致地添加为CHAR和BINARY数据类型，无论是否允许NULL。

第二个结果来自关闭ANSI_PADDING时创建的表。可为空的第一个CHAR列已被修剪。具有NOT NULL约束的CHAR列用空格填充。无论是否可以为空，VARBINARY列都修剪了尾随零。VARCHAR列修剪了尾随空格。

第三个结果来自一个表变量，该变量也是通过将ANSI_PADDING设置为OFF来创建的，该设置完全无害。无论设置如何，它的行为都与ANSI兼容。

如果您正在努力接受所有规则和例外，那么您并不孤单。

查找使用ANSI_PADDING关闭创建的异常列

在访问表时，无论您对ANSI_PADDING进行了何种设置，查询行为都是一致的。该设置将保留在表列中，而连接设置将被忽略。无论使用何种连接设置访问“旧版”数据库，该数据库都能始终如一地运行。我们可以通过查询元数据来检查临时表发生了什么。

USE tempdb
SELECT S.name AS TheColumn,
  Object_Schema_Name(S.object_id) + '.' + Object_Name(S.object_id) AS TableName,
  is_ansi_padded
  FROM sys.columns AS S
    INNER JOIN sys.tables AS t
      ON t.object_id = S.object_id
  WHERE system_type_id IN (165, 167, 173, 175) 
     AND is_ansi_padded = 0; --ansi padding off!!

sys.columns视图中的列如果ANSI_PADDING处于打开状态，则为1；如果关闭，则为0。该查询将非常快速地告诉您数据库是否有设置为ANSI_PADDING off的异常列（只需去掉第一行“USE tempdb”）。

结论

除非有人最终有意或无意关闭ANSI_PADDING的危险消失，否则在从SQL Server中最终删除该功能之前，请始终在执行表CREATE语句之前使用SET ANSI_PADDING ON设置与ANSI行为的连接，但在其他任何地方都不应使用的设置，因为该设置和支持已计划弃用，此时您将无法关闭ANSI兼容性。