Поведение уникального индекса, столбца varchar и (пустых) пробелов

Я использую 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 ряд)

Сообщение 2601, уровень 14, состояние 1, строка 8

Не удается вставить повторяющуюся ключевую строку в объект «dbo.Test» с уникальным индексом «UniqueIndex». Повторяющееся значение ключа (sample).

Заявление было прекращено.

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

>образец<

(затронуты 1 ряд)

Мои вопросы:

  1. Я предполагаю, что индекс не может хранить конечные пробелы. Может ли кто-нибудь указать мне на официальную документацию, которая определяет/определяет это поведение?
  2. Есть ли настройка, чтобы изменить это поведение, то есть заставить его распознавать «образец» и «образец» как два разных значения (кстати, они есть), чтобы оба они могли быть в индексе.
  3. С какой стати SELECT возвращает строку? SQL Server должен делать что-то действительно забавное/умное с пробелами в предложении WHERE, потому что, если я удалю уникальность в индексе, обе команды INSERT будут работать нормально, а SELECT вернет две строки!

Любая помощь/указатель в правильном направлении будет оценена по достоинству. Спасибо.


sql-server tsql string-comparison unique-index
person Eric    schedule 27.02.2012    source источник

Ответы (1)


arrow_upward
18
arrow_downward

объяснение концевых пробелов:

SQL Server следует спецификации ANSI/ISO SQL-92 (раздел 8.2, Общие правила №3) относительно сравнения строк с пробелами. Стандарт ANSI требует заполнения строк символов, используемых при сравнении, чтобы их длины совпадали перед их сравнением. Заполнение напрямую влияет на семантику предикатов предложений WHERE и HAVING и других сравнений строк Transact-SQL. Например, Transact-SQL считает строки «abc» и «abc» эквивалентными для большинства операций сравнения.

Единственным исключением из этого правила является предикат LIKE. Когда в правой части выражения предиката LIKE есть значение с пробелом в конце, SQL Server не дополняет два значения до одинаковой длины до того, как произойдет сравнение. Поскольку целью предиката LIKE по определению является облегчение поиска по шаблону, а не простая проверка на равенство строк, это не нарушает упомянутый ранее раздел спецификации ANSI SQL-92.

Вот хорошо известный пример всех случаев, упомянутых выше:

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

Ниже приведены дополнительные сведения об завершающих пробелах и предложении LIKE.

Относительно индексов:

Вставка в столбец, значения которого должны быть уникальными, завершится ошибкой, если вы укажете значение, которое отличается от существующих значений только конечными пробелами. Все следующие строки будут считаться эквивалентными по уникальному ограничению, первичному ключу или уникальному индексу. Аналогичным образом, если у вас есть таблица с приведенными ниже данными, и вы попытаетесь добавить уникальное ограничение, это не удастся, поскольку значения считаются идентичными.

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

(Взято с здесь.)

person Oleg Dok    schedule 27.02.2012
comment
Спасибо за подсказки, ребята. Моя вина за то, что я поленился гуглить это сам. На мой взгляд, поведение, определенное стандартом, не является интуитивным. Я полагаю, что 9 из 10 разработчиков сказали бы, что «a» и «a» НЕ являются одной и той же строкой, ну да ладно. - person Eric; 27.02.2012
comment
Это одна из самых неинтуитивных вещей, с которыми я сталкивался в Azure SQL до сих пор... - person Jangcy; 20.06.2018