Varchar with trailing spaces as a Primary Key in SQL Server 2008

Question

Is it possible to have a varchar column as a primary key with values like 'a ' and 'a', is gives always this error "Violation of PRIMARY KEY constraint" in MS SQL Server 2008. In Oracle dons't give any error. BTW I'm not implementing this way I'm only trying to migrate the data from oracle to sql server.

Regards

Answer 1

The SQL-92 standard dictates that for character string comparison purposes, the strings are padded to be the same length prior to comparison: typically the pad character is a space.

Therefore 'a' and 'a ' compare EQUAL and this violates the PK constraint. http://support.microsoft.com/kb/316626

I could find nothing to indicate this behaviour has changed since then.

You may get away with using varbinary instead of varchar but this may not do what you want either.