ANSI_Padding设置

Question

最近，我开始研究Server ANSI_Padding设置。在阅读了BOL和一些在线文章之后，我发现自己有更多的问题和困惑。

我知道最好还是坚持使用ANSI_Padding On，并且当设置更改为ON或OFF时，我了解NULL和NOT NULL列的不同行为。

但我不能理解这个概念。填充物有什么用途？在二进制数字(或字符串中的空格)中添加零不会改变值吗？

二进制数0x00ee等于0x00ee (至少在转换为十进制时)，但是0x00ee00如何与0x00ee相同？

字符串'a‘(1空格)与'a’(没有空格)不一样，但它们在char(3)列中都变成'a‘(3个空格)。这怎么能被接受呢？

在裁剪值时，也存在同样的混淆。

BOL似乎确实提到了ANSI_Padding用于控制值的存储方式。所以我最初的猜测是，实际值没有改变。但是BOL没有详细说明这一点，我至今还没有找到任何文章。

谢谢你的帮助。

Answer 1

首先，下面是是最近的一篇文章，它展示了事情是如何运作的。

还有这篇文章来自微软，解释了当字符串有尾随空格时Server是如何处理比较的。

为什么有垫子？对于所有数据都将非常接近相同长度的NOT NULL列，将所有内容填充到(例如)10个字符可能会比添加使列变长的开销所消耗的空间更少。我认为使列NULL成为可能需要与使其可变长度相同的开销，因此包括填充成为可选的。

当您提到BOL正在讨论如何存储这些值时-是的，这确实改变了值。如果将'a' (没有空格)和'a ' (一个空格)存储在char(3) NOT NULL列中，它们都将被检索为'a‘(两个空格)。在char(3) NULL列中，它们将被检索为'a'或'a '，这取决于创建表(或列)时的ANSI_PADDING设置。

为什么失去或保留尾随空间似乎如此不重要？

基本上，当比较两个字符串时，SQL会将较短的字符串与空格放在一起，直到它们的长度相同为止。所以：

SELECT CASE WHEN 'a' = 'a ' THEN 'TRUE' ELSE 'FALSE' END as String_Comp;

返回TRUE。

同样- binary()和varbinary()不应该仅仅被看作是数字的二进制表示(当然，它们是)，而是二进制字符串。在二进制值作为二进制文件的比较中，SQL不注意尾随零。

一个例子: 0X00EE00是十进制数60928。0X00EE是十进制数238。

SELECT CASE WHEN CAST(0x00EE00 as int) = CAST(0x00EE as int)
         THEN 'TRUE'
         ELSE 'FALSE'
       END as Binary_Number_Comp;

返回FALSE (60928 <> 238)。然而：

SELECT CASE WHEN CAST(0x00EE00 as binary(10)) = CAST(0x00EE as varbinary(6))
         THEN 'TRUE'
         ELSE 'FALSE'
       END as Binary_String_Comp;

返回TRUE；正如Max所指出的，尾随零的作用就像字符串中的空终止符，并且没有包含在比较中。

有趣的是：

SELECT CASE WHEN 0x00EE00 = 0x00EE THEN 'TRUE' ELSE 'FALSE' END as Binary_Value_Comp;

返回TRUE，因此默认情况下，这是将值作为二进制字符串进行比较，而不是二进制数字。

根据上面的文档，LIKE比较是区分基于尾随零的字符串值的唯一方法。

SELECT CASE WHEN 'a' LIKE 'a ' THEN 'TRUE' ELSE 'FALSE' END as LIKE_String_Comp;

返回FALSE。

因此，总之，除非您真的在使用它，否则Server会忽略尾随空格，而不管ANSI_PADDING设置如何，这就是为什么对它们进行裁剪或不修剪的原因。

顺便说一句，下面是一个ANSI_PADDING影响的快速例子

查询：

SET ANSI_PADDING ON;

IF (OBJECT_ID('tempdb..#test') IS NOT NULL) DROP TABLE #test;

CREATE TABLE #test
     ( my_char CHAR(10) NOT NULL
      ,my_varchar VARCHAR(10) NOT NULL
      ,my_char_NULL CHAR(10) NULL
      ,my_binary BINARY(6) NOT NULL
      ,my_varbinary VARBINARY(6) NOT NULL
      ,my_binary_NULL BINARY(6) NULL
     );

INSERT INTO #test
VALUES ('ABC   ','ABC   ','ABC   ',0xABC000,0xABC000,0xABC000);

PRINT 'ANSI_PADDING ON:';

SELECT '|' + my_char + '|' as my_char
      ,'|' + my_varchar + '|' as my_varchar
      ,'|' + my_char_NULL + '|' as my_char_NULL
      ,my_binary
      ,my_varbinary
      ,my_binary_NULL
  FROM #test;

SET ANSI_PADDING OFF;

IF (OBJECT_ID('tempdb..#test2') IS NOT NULL) DROP TABLE #test2;

CREATE TABLE #test2
     ( my_char CHAR(10) NOT NULL
      ,my_varchar VARCHAR(10) NOT NULL
      ,my_char_NULL CHAR(10) NULL
      ,my_binary BINARY(6) NOT NULL
      ,my_varbinary VARBINARY(6) NOT NULL
      ,my_binary_NULL BINARY(6) NULL
     );

INSERT INTO #test2
VALUES ('ABC   ','ABC   ','ABC   ',0xABC000,0xABC000,0xABC000);

PRINT 'ANSI_PADDING OFF:';

SELECT '|' + my_char + '|' as my_char
      ,'|' + my_varchar + '|' as my_varchar
      ,'|' + my_char_NULL + '|' as my_char_NULL
      ,my_binary
      ,my_varbinary
      ,my_binary_NULL
  FROM #test2;

结果：

ANSI_PADDING ON:
my_char      my_varchar   my_char_NULL my_binary      my_varbinary   my_binary_NULL
------------ ------------ ------------ -------------- -------------- --------------
|ABC       | |ABC   |     |ABC       | 0xABC000000000 0xABC000       0xABC000000000


ANSI_PADDING OFF:
my_char      my_varchar   my_char_NULL my_binary      my_varbinary   my_binary_NULL
------------ ------------ ------------ -------------- -------------- --------------
|ABC       | |ABC|        |ABC|        0xABC000000000 0xABC0         0xABC0

注意:正如Max Vernon在他的回答中指出的那样，我上面的笔记都是关于Server如何处理事情的。在从Server检索数据的任何应用程序中，值的相等性很可能完全不同。