Trailing space differences?

adamtuliper

In two seemingly identical dbs, differences are reported on an nvarchar column which is empty in both database - not null either.

When I show the differences in the column by clicking on it to bring up the compare window (and show whitespace) it shows me two spaces in one of the dbs and none in the other. Of course, the comparison differences go away if I select "Trim Trailing Spaces" but I'm a bit confused why it's there.

When I query both tables for that record, they both report a length of zero. Again - this column is not null.

select len(UserCode) from where CustomerId=2878941

yields "0" in both databases but sql data compare shows the length as two spaces?

using sql server's sp_help
sp_help Customer
it shows TrimTrailingBlanks is not on in either database for this column.

so I'm a bit confused why the product is showing differences?
Thanks!

Brian Donahue

Hello Adam,

SQL Data Compare turns all data into .NET string objects before comparing, so it's expected that two strings with varing lengths of whitespace will show as a difference. This is why the "trim trailing spaces" option was added to the program.

SQL Server's LEN function also trims trailing spaces on variable-length data, so a field with nothing but two spaces and a field with three will show as the same length. If you need to see the actual size of the data you can get SQL Server to show you the length in bytes using the DATALENGTH() function.

Hope this helps!