|
我正在使用Microsoft SQL Server 2008 R2(带有最新的Service Pack / patches),数据库排序规则是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)
我的问题是：
>我假设索引无法存储尾随空格.任何人都可以指向我指定/定义此行为的官方文档吗？
>是否有一个设置可以改变这种行为,也就是说,让它将’sample’和’sample’识别为两个不同的值(顺便说一句,它们都是这样),因此两者都可以在索引中.
>为什么地球上的SELECT会返回一行？ SQL Server必须使用WHERE子句中的空格做一些非常有趣/聪明的事情,因为如果我删除索引中的唯一性,两个INSERT都将运行正常,SELECT将返回两行！
任何帮助/指针在正确的方向将不胜感激.谢谢.
解决方法
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.
06001
(摘自here.) （编辑：东莞站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
