Avoid trailining spaces on insert

Question

In SQL Server 2008 I have below table created:

CREATE TABLE [Items](
    [ID] [uniqueidentifier] NOT NULL,
    [Stock_ID] [uniqueidentifier] NOT NULL,
    [Code] [nvarchar](36) NOT NULL,
    [RFCODE] [nvarchar](24) NOT NULL
) 

When I insert a new row:

insert into Items 
values ('34438913-6CB0-43AD-9DD5-5FBFFC60B313', '70667244-6F03-4113-930E-BF66CF1C29EE', 'MX7A386CHGR5WW', 'BE0000000000000000000162')

Code column has length 36, which means SQL Server adds trailing spaces to MX7A386CHGR5WW.

How can avoid this behaviour? (code column can vary from 10 to 36 chars)

Answer 1

NO - SQL Server does NOT add any trailing spaces to a column that's defined as nvarchar!

It will store exactly as many characters as you pass in - no more!

The char and nchar datatypes will do this padding to the defined length - but NOT the (n)varchar types!

If you run this query after your insert, you'll see that your statement is wrong:

SELECT 
    *, LEN(Code), '[' + Code + ']'
FROM Items

You'll see 14 for the length of the Code column, and putting square brackets around the Code column clearly shows that no trailing spaces have been added.

If you add a column of type NCHAR(36) to your table

ALTER TABLE Items 
ADD CodeFixed NCHAR(36)

and then set it's value to MX7A386CHGR5WW

UPDATE Items
SET CodeFixed = 'MX7A386CHGR5WW'
WHERE ID = '34438913-6CB0-43AD-9DD5-5FBFFC60B313'

then when you check, you'll see that the nchar(36) is in fact padded with spaces to the defined maximum length:

SELECT 
    LEN(Code), '[' + Code + ']',
    '[' + CodeFixed + ']'
FROM Items