INF: Insertion of Strings Containing Trailing Spaces (231830)

SUMMARY

With ANSI_PADDING ON, SQL Server follows the ANSI SQL-92 standard for string padding when you insert strings into varchar columns. Per the ANSI standard, trailing spaces are not removed from values inserted into varchar columns; they are stored as part of the data.

When ANSI_PADDING is OFF, trailing spaces are removed from a value before it is inserted into a varchar column. This causes values differentiated only by trailing spaces to be stored identically by the server.

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.

PaddedColumn

------------
'abc'
'abc '
'abc  '
'abc    '

etc...

The same principles apply to updates of data which have trailing spaces.

MORE INFORMATION

Some of the errors you may receive when inserting data that differs only by trailing spaces, or when adding an index or constraint on such data, are:

Server: Msg 2627, Level 14, State 1
Violation of PRIMARY KEY constraint 'constraint_name'. Cannot insert duplicate key in object 'table_name'.
The statement has been terminated.

Server: Msg 2627, Level 14, State 2
Violation of UNIQUE KEY constraint 'constraint_name'. Cannot insert duplicate key in object 'table_name'.
The statement has been terminated.

Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'table_name' with unique index 'index_name'.

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID %d. Most significant primary key is '%S_KEY'.

For additional information on how to query data with trailing spaces, click the article number below to view the article in the Microsoft Knowledge Base:

316626 INF: How SQL Server Compares Strings With Trailing Spaces