sql-server - 唯一索引、varchar 列和(空白)空格的行为

Question

我使用的是 Microsoft SQL Server 2008 R2(带有最新的服务包/补丁)，数据库排序规则为 SQL_Latin1_General_CP1_CI_AS。

以下代码:

SET ANSI_PADDING ON;
GO

CREATE TABLE Test (
   Code VARCHAR(16) NULL
);
CREATE UNIQUE INDEX UniqueIndex
    ON Test(Code);

INSERT INTO Test VALUES ('sample');
INSERT INTO Test VALUES ('sample ');

SELECT '>' + Code + '<' FROM Test WHERE Code = 'sample        ';
GO

产生以下结果:

(1 row(s) affected)

Msg 2601, Level 14, State 1, Line 8

Cannot insert duplicate key row in object 'dbo.Test' with unique index 'UniqueIndex'. The duplicate key value is (sample ).

The statement has been terminated.

‐‐‐‐‐‐‐‐‐‐‐‐

>sample<

(1 row(s) affected)

我的问题是:

1. 我假设索引不能存储尾随空格。谁能给我指出指定/定义此行为的官方文档？
2. 是否有一个设置可以更改此行为，也就是说，使其将“sample”和“sample”识别为两个不同的值(顺便说一句，它们确实如此)，以便两者都可以出现在索引中。
3. 到底为什么 SELECT 会返回一行？ SQL Server 一定对 WHERE 子句中的空格做了一些非常有趣/聪明的事情，因为如果我删除索引中的唯一性，两个 INSERT 都将运行正常，并且 SELECT 将返回两行!

任何正确方向的帮助/指针将不胜感激。谢谢。

Answer 1

Trailing blanks explained :

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.

这是上述所有案例的一个众所周知的示例:

DECLARE @a VARCHAR(10)
DECLARE @b varchar(10)

SET @a = '1'
SET @b = '1 ' --with trailing blank

SELECT 1
WHERE 
    @a = @b 
AND @a NOT LIKE @b
AND @b LIKE @a

以下是有关 trailing blanks and the LIKE clause 的更多详细信息.

关于索引:

An insertion into a column whose values must be unique will fail if you supply a value that is differentiated from existing values by trailing spaces only. The following strings will all be considered equivalent by a unique constraint, primary key, or unique index. Likewise, if you have an existing table with the data below and try to add a unique restriction, it will fail because the values are considered identical.

PaddedColumn
------------
'abc'
'abc '
'abc  '
'abc    '

(取自 here 。)