Automatyczne przycinanie SQL Server wartości varchar w równym porównaniu, ale nie w porównaniu

Dzisiaj spotkałem ciekawe zachowanie SQL Server (zaobserwowane w 2005 i 2012 roku), które miałem nadzieję, że ktoś może to wyjaśnić.

Zapytanie dokonujące porównania za =pomocą pola NVARCHAR zignorowało końcowe miejsce w ciągu (lub automatycznie przycięło wartość przed porównaniem), ale to samo zapytanie z likeoperatorem nie zignorowało tego miejsca. Stosowane zestawienie to Latin1_General_CI_AS w 2012 r.

Rozważ to SQL Fiddle: http://sqlfiddle.com/#!6/72262/4

Zauważ, że likeoperator nie zwraca wyniku dla ciągu końcowego spacji, ale =robi to. Dlaczego to?

Punkty bonusowe: Nie jestem w stanie powielić tego na polu VARCHAR, pomyślałbym, że w obu typach danych miejsce będzie obsługiwane w ten sam sposób - czy to prawda?

Moja wstępna odpowiedź sugerowała, że ​​flaga ANSI_PADDING ustawiona na OFF może być przyczyną różnicy w zachowaniu. Jest to jednak nieprawidłowe; ta flaga ma wpływ tylko na pamięć, ale nie na porównanie równości.

Różnica wynika z implementacji standardu SQL przez Microsoft . Norma stwierdza, że ​​podczas sprawdzania równości oba łańcuchy po lewej i prawej stronie operatora równości muszą być wypełnione, aby mieć tę samą długość . To wyjaśnia następujące wyniki:

insert into test_padding (varchar_clmn, nvarchar_clmn) values ('space ', 'nspace ')
go
-- equality for varchar column
select count(*) from test_padding where varchar_clmn = 'space' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space    ' --returns 1
-- equality for nvarchar column
select count(*) from test_padding where nvarchar_clmn = 'nspace' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace    ' --returns 1

Operator LIKE nie wstawia operandów. Zachowuje się również inaczej dla typów kolumn VARCHARiNVARCHAR :

-- likeness for varchar column
select count(*) from test_padding where varchar_clmn like 'space' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space    ' -- returns 0
-- likeness for nvarchar column
select count(*) from test_padding where nvarchar_clmn like 'nspace' -- returns 0
select count(*) from test_padding where nvarchar_clmn like 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn like 'nspace    ' -- returns 0

Zachowanie operatora LIKE dla typu ASCII jest specyficzne dla SQL Server; dla typu Unicode jest zgodny z ANSI.

SQL narodził się w czasach, gdy większość języków przetwarzania danych używała stałych długości dla każdego pola / zmiennej. Automatyczne uzupełnianie pól tekstowych dodatkowymi spacjami również było częścią tego obrazu. W celu zachowania zgodności z tym zachowaniem oryginalny typ CHAR SQL został jawnie zdefiniowany, aby jego operator „=” ignorował końcowe spacje. (Jeśli uważasz, że to dziwne, pokaż mi przekonujący przypadek, w którym końcowe spacje dołączane do tekstu mają rzeczywiste znaczenie biznesowe ).

Od tego czasu typy SQL CHAR ewoluowały we wszystkich kierunkach, ale nie jest wykluczone, że niektóre bardziej nowoczesne typy danych nadal odziedziczyły pewne cechy charakterystyczne po swoich historycznych poprzednikach.

W dokumentacji LIKE (Transact-SQL) Microsoft pisze (moje wyróżnienie):

Dopasowywanie wzorców za pomocą LIKE

LIKE obsługuje dopasowanie wzorca ASCII i dopasowanie wzorca Unicode. Gdy wszystkie argumenty ... są typami danych znakowych ASCII, następuje dopasowanie wzorca ASCII. Jeśli jeden z argumentów jest typu danych Unicode, wszystkie argumenty są konwertowane na Unicode i wykonywane jest dopasowanie wzorca Unicode. Gdy używasz danych Unicode ... z LIKE, końcowe spacje są znaczące; jednak w przypadku danych innych niż Unicode końcowe spacje nie są znaczące. Unicode LIKE jest zgodny ze standardem ISO. ASCII LIKE jest kompatybilny z wcześniejszymi wersjami SQL Server.