Trailing whitespaces in MSSQL
Legacy is hell. It has been proven to me again while writing an application that replaces the old Visual Basic 1.0 scripts.
These scripts copied data from an MS Access database to an MSSQL database.
The effort to write these scripts was clearly less than porting the scripts that access the MS Access database.
I noticed that when I did inserts from Ms Access to MSSQL some fields values were padded with white space.
These white spaces were not present in the corresponding fields in Ms Access, so it was clearly MSSQL that manipulated the values before they were stored in these fields.
After some googling, I discovered that this kind of padding is enabled by default on nchar data types. Also, when originally a field had the nchar datatype and was converted later to something else, e.g. nvarchar, the padding is still enabled.
A workaround is to create a query that does RTRIM on all these whitespace padded fields:
UPDATE set someField=RTRIM(someField)