This article was previously published under Q231830
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.