Why are values stored in an NVARCHAR column someti

问题:

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.

回答1:

NCHAR pads the field, NVARCHAR doesn't. But if the data is from the old field then the spaces will remain until trimmed.

回答2:

If you have converted them previously from NCHAR to NVARCHAR, any data that was previously entered will still contain the trailing spaces. You can update them all by:

UPDATE tablename
SET column = RTRIM(column)

回答3:

The "legacy" spaces caused by the nchar type previously persist because of SET ANSI_PADDING ON which is the default.

You need to UPDATE with an RTRIM to remove trailing spaces.

回答4:

You can sometimes do this:

Update MyTable Set MyColumn = LTRIM(RTRIM(MyColumn));

回答5:

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

回答6:

How are you getting the values into the table? I think it's possible that if you have a stored proc and you explicitly set the length of the parameter that updates this column to 50 (say via SqlCommand), it will end up padding it on the ADO.NET side already.

回答7:

I came across this thread while trying to solve this exact same problem and will post my solution.

I had used NChar as this is my first SQL database and I didn't know about the padded spaces. I then created a LINQ-to-SQL class to provide access to my database. When I later updated my database I didn't update my LINQ-to-SQL class so it still had NChar in the code behind. I changed them to fix the problem.

If you didn't use this exact method you may have a similar solution for your method. Something you use to add records hasn't been updated.