Article ID: 231830 - Last Review: October 17, 2003 - Revision: 3.2 INF: Insertion of Strings Containing Trailing SpacesThis article was previously published under Q231830 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... 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'. 316626
(http://support.microsoft.com/kb/316626/EN-US/
)
INF: How SQL Server Compares Strings With Trailing Spaces
| Article Translations
|
| United States | Change | | | All Microsoft Sites |
Help and Support
Back to the top
|
