Menu
An app I'm working on is storing Unicode strings in an NVARCHAR(50) column in an SQL Server 2005 database.
Sometimes, the database returns a string padded with spaces up to the max length of the column (50). At other times the padding doesn't happen.
I think that the type of this column was originally specified as NCHAR, but that when we realized the spaces were being added, we changed it to NVARCHAR(50). Could this have anything to do with it?
Either way, can this 'feature' be turned off?
Clarification
I just realized that what I wrote above doesn't make it clear that even newly-inserted strings are getting padded with spaces.
846
An NVARCHAR value can include tabs, spaces, and nonprintable characters. Nonprintable NVARCHAR characters are entered, displayed, and treated in the same way as nonprintable VARCHAR characters.
When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs.
Takeaway: According to SQL Server, an identifier with trailing spaces is considered equivalent to the same identifier with those spaces removed.
The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.
NCHAR pads the field, NVARCHAR doesn't. But if the data is from the old field then the spaces will remain until trimmed.
145
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
