Does SQL Server trim trailing spaces when insert on nchar field? What about String or Binary would be truncated?

Question

User-943250815 posted

Consider a table with a City field as nchar(15).

INSERT INTO TblCities (City) VALUES (N'NEW YORK       ');       /* value with 15 chars, record inserted OK */
INSERT INTO TblCities (City) VALUES (N'NEW YORK            ');  /* value with 20 chars, record inserted OK no error */
INSERT INTO TblCities (City) VALUES (N'NEW YORK           1');  /* value with 20 chars, exception String or Binary would be truncated */

In 2nd insert string is just truncated, it was not supposed to get an exception like 3nd insert?
Is it possible that 2nd insert sometimes generate exception and sometimes not?

Answer 1

User77042963 posted

Maybe you need a variable length for the city column.

But your sample data are inserting OK.

create table TblCities(City   nchar(20))
 
INSERT INTO TblCities (City) VALUES (N'NEW YORK       ');        
INSERT INTO TblCities (City) VALUES (N'NEW YORK            ');  
INSERT INTO TblCities (City) VALUES (N'NEW YORK           1');

Select * 
,len(City) 
, datalength(City) 
from   TblCities

drop table TblCities

Answer 2

User-943250815 posted

Thank you, but my questions (conceptual) are about SQL Server behavior.
For 2nd insert I was expecting an Exception on SQL (at least in my mind).

Answer 3

User-1330468790 posted

Hi jzero,

I tried to do the sql insert operation for both 20-characters strings, one appended with spaces and another one not.

The result is the same as you said. The one with spaces has been inserted successfully and another has got error.

 

I have searched for the reason but failed since there is no explicit explanation for that.

However, I could infer the reason behind this phenomenon by reading below two links:

• nchar and nvarchar (Transact-SQL) 
• SET ANSI_PADDING

When you insert a string value into a NCHAR column, SQL Server will add trailing spaces to the string value to the length declared in the column if the length of the string value is less than the length specified in the column. However, when you select this string value, SQL Server removes the trailing spaces before returning it. On the other hand, if you insert a string which contains characters exceeding the length of the column, SQL Server issues an error message. What about the trailing space? From the test result, I think the trailing space will be ignored for the "ANSI_WARNINGS".

For example, if you set "ANSI_WARNINGS" to OFF, you won't get the error message anymore.

SET ANSI_WARNINGS  OFF;

  

Above is just my opinion. Hope this could help you.

Best regards,

Sean

Answer 4

User753101303 posted

Hi,

I can understand though that if characters beyond the max length are whitespaces they can be trimmed. Not sure if this behavior is part of the SQL "standard" or what would happen with other databases.

Edit: I gave this a try with http://sqlfiddle.com/ It seems to work the same way for most databases except Oracle (at least with their configuration).

Answer 5

User-943250815 posted

Thanks Sean, Patrice. I can understand also. Let´s say it is an acceptable behavior.