Ciekawostki MSSQL: spacje na końcu stringów

Co wydrukuje poniższy skrypt T-SQL?

DECLARE
@string1 nvarchar(100),
@string2 nvarchar(100)

SET @string1 = 'abc'
SET @string2 = 'abc '

PRINT 'test1:'

IF @string1 = @string2
PRINT N'równe'
ELSE
PRINT N'nie równe'

PRINT 'test2:'

IF LEN(@string1) = LEN(@string2)
PRINT N'równe'
ELSE
PRINT N'nie równe'

PRINT 'test3:'

IF DATALENGTH(@string1) = DATALENGTH(@string2)
PRINT N'równe'
ELSE
PRINT N'nie równe'

Prawidłowa odpowiedź dla wszystkich wersji SQL Server i dowolnych ustawień sesji:

test1:
równe
test2:
równe
test3:
nie równe

Powyższy przykład pokazuje, jak SQL Server porównuje łańcuchy znaków zawierające na końcu jedną lub kilka spacji (tzw. trailing spaces). W skrypcie użyłem zmiennych, ale zachowanie jest identyczne w przypadku zapytań i warunków WHERE czy HAVING. Co ciekawe, obsługa ta jest zgodne ze standardem ANSI SQL-92, który zakłada, że porównywane łańcuchy znaków powinny być tej samej długości. Jeżeli nie są, krótszy ma być automatycznie uzupełniony odpowiednią ilością spacji przez sam silnik w momencie przetwarzania zapytania. Dlatego właśnie spacje na końcu stringów są ignorowane w logice porównań przez SQL Server. Jedynym sposobem na rozróżnienie stringa ze spacjami i stringa bez spacji jest użycie operatora LIKE na następującej zasadzie:

-- wyłącznie rekordy ze spacjami na końcu
SELECT col FROM tab WHERE col LIKE 'abc %'

Warto też pamiętać o ustawieniu ANSI_PADDING, od którego zależy zachowanie silnika podczas operacji INSERT (ustawienie to w ogóle nie wpływa na sytuację porównań, więc odczytów - nie modyfikuje w żaden sposób działania powyższego skryptu). Jeżeli ANSI_PADDING jest włączone, SQL Server traktuje końcowe spacje jako część danych - natomiast przy wyłączonym ustawieniu ANSI_PADDING, spacje są automatycznie ucinane, jak poniżej:

SET ANSI_PADDING OFF;

CREATE TABLE #tmp
(
col varchar(10)
);
GO

INSERT INTO #tmp (col) VALUES ('abc ');
GO

SELECT DATALENGTH(col) FROM #tmp; -- zwraca 3

-- DROP TABLE #tmp

Mała uwaga (patrz komentarze): mechanizmowi ucinania końcowych spacji przy operacji INSERT nie podlegają stringi o unikodowym typie danych (nvarchar, nchar). Natomiast przy ANSI_PADDING ustawionym na OFF, char zachowuje się analogicznie jak varchar (spacje są również ucinane) - o ile kolumna dopuszcza NULLe. W sytuacji, kiedy kolumna typu char jest NOT NULL, to niezależnie od ustawienia ANSI_PADDING, spacje nie są ucinane.

Aktualizacja: jak trafnie zauważył Jedrzej_g (patrz komentarze), obsługa trailing spaces zależy bezpośrednio od właściwości kolumny UsesAnsiTrim, której wartość zależy z kolei od ustawień sesji (ANSI_PADDING) w momencie tworzenia tabeli. Może uda mi się to opisać dokładniej w jednym z przyszłych postów - żeby już nie komplikować tego.

Więcej informacji:
KB316626: INF: How SQL Server Compares Strings with Trailing Spaces
KB231830: INF: Insertion of Strings Containing Trailing Spaces
KB154886: INF: Behavior of ANSI_PADDING