SQL Server e gli spazi finali

Per quanto riguarda la comparazione di due stringhe contenenti spazi SQL Server rispetta lo standard ANSI/ISO SQL-92 – Sezione 8.2 <Comparison Predicate> – General rules 3 che riporta quanto segue:

The comparison of two character strings is determined as follows:

a) If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad characters, where the pad character is chosen based on CS. If CS has the NO PAD attribute, then the pad character is an               implementation-dependent character different from any character in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a <space>.

b) The result of the comparison of X and Y is given by the collating sequence CS.

c) Depending on the collating sequence, two strings may compare as equal even if they are of different lengths or contain different sequences of characters. When the operations MAX, MIN, DISTINCT, references to a grouping column, and the UNION, EXCEPT, and INTERSECT operators refer to character strings, the specific value selected by these operations from a set of such equal values is implementation-dependent.

Note: If the coercibility attribute of the comparison is Coercible, then the collating sequence used is the default defined for the character repertoire. See also other Syntax Rules in this Subclause, Subclause 10.4, “<character set specification>”, and Subclause 11.28, “<character set definition>”.

Quindi come riportato nella KB316626 INF: How SQL Server Compares Strings with Trailing Spaces due stringhe che differiscono solo per spazi finali sono considerate uguali:

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, <Comparison Predicate>, 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.

Questo significa anche che se due tabelle sono in relazione tramite una colonna varchar gli spazi finali non costituiranno una violazione della chiave esterna.

Si consideri ad esempio il seguente scenario costituito dalle tabelle Products e Sales:

CREATE TABLE Products
(
     Id NVARCHAR(20) primary key
);
GO

CREATE TABLE Sales
(
     ProductId NVARCHAR(20) references Products(Id),
     Quantity INT
);
GO

INSERT INTO Products (Id) values (‘A’);
GO

Se si prova ad eseguire i seguenti comandi di INSERT solo il terzo fallirà, mentre il secondo sarà eseguito senza problemi:

— Inserimento del record Sales relativo al Product ‘A’
INSERT INTO Sales (ProductId, Quantity) values (‘A’, 23);
GO

— Inserimento del record Sales relativo al Product ‘A ‘
INSERT INTO Sales (ProductId, Quantity) values (‘A ‘, 45);
GO

— Inserimento del record Sales relativo al Product ‘B’
INSERT INTO Sales (ProductId, Quantity) values (‘B’, 67);
GO

Se si intende bloccare l’inserimento di valori che differiscono solo per spazi finali l’approccio a mio avviso più corretto è quello di aggiungere nella tabella figlio ovvero la Sales nello scenario di esempio un vincolo che impedisca l’inserimento di valori che terminano con uno spazio:

CREATE TABLE Sales
(
     id NVARCHAR(20) references Products(Id),
     Quantity INT,
     CONSTRAINT CK_Sales_ProductId CHECK (RIGHT(ProductId,1) <> ‘ ‘)
);
GO

Un’alternativa potrebbe essere l’utilizzo dell’impostazione ANSI_PADDING a OFF che però oltre a non essere consigliata ha una serie di particolarità da tenere presente a riguardo si veda SET ANSI_PADDING (Transact-SQL) e la KB316626:

Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.

This setting affects only the definition of new columns. After the column is created, SQL Server stores the values based on the setting when the column was created. Existing columns are not affected by a later change to this setting.

We recommend that ANSI_PADDING always be set to ON.

The SET ANSI_PADDING setting does not affect whether SQL Server pads strings before it compares them. SET ANSI_PADDING only affects whether trailing blanks are trimmed from values being inserted into a table, so it affects storage but not comparisons.

Va comunque precisato che sebbene lo scenario presentato può essere comune rappresenta comunque una cattiva implementazione in quanto le chiavi primarie dovrebbero rispettare le seguenti regole:

• Le chiavi primarie dovrebbero essere le più piccole possibili e possibilmente dei valori numerici (i valori interi rappresentano la scelta migliore)
• Le chiavi primarie non dovrebbero mai essere modificate
• Le chiavi primarie non dovrebbero essere degli identificativi puri senza portare alcune informazione. Quindi sono da evitare come chiavi primarie codici articolo, codici fiscali, partita iva, codici di previdenza sociale, numeri di carta d’identità, numeri passaporto, etc.. in quanto tali valori seppur raramente potrebbero dover cambiare. Si pensi ad esempio al caso in cui un’azienda in seguito ad una acquisizione sia costretta a rivedere la sua codifica articoli.