Alex Rivera | Logout

Why are values stored in an NVARCHAR column sometimes padded with trailing spaces?

Asked 2011-04-21T15:18:19.290
17

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.

c# sql-server-2005
Edit
Report

1 Answer

1

NCHAR columns are padded with spaces because CHARACTER is a fixed-width datatype as required by SQL standard:

If VARYING is not specified in <character string type>, then the length in characters of the character string is fixed and is the value of <length>.

and

Let T and V be a TARGET and VALUE specified in an application of this Subclause

…

If the data type of Tis fixed-length character string with length in characters L and the length in characters M of V is less than L, then the first M characters of T are set to V and the last L-M characters of T are set to <space>s.

Note that ANSI_PADDING = ON (which is default) functions and expressions other than concatenation and comparison implicitly truncate the trailing spaces on VARCHAR arguments:

SELECT  LEN(a), LEN(b), LEN(a + b)
FROM    (
        VALUES
        (CAST('a ' AS VARCHAR(100)), CAST('b' AS VARCHAR(100)))
        ) AS q(a, b)

-----------
1    1    3
answered 2011-04-21T15:34:03.773

Your Answer