Question

K_Srinivas on Wed, 18 Mar 2015 04:08:39


Hi All,

Can anyone please help me resolving the below issue?

I have created a SQL table with multiple columns to which I am inserting data from an XML file via SSIS. When the data got inserted in to the table, the value of the column is using the total length of the field rather only required space.

e.g. There is a column 'FileName' which is of nchar(2000) and the actual file name size is 150 characters. So, it is taking total length by giving 150 characters for FileName and remaining length for space. 

I don't want to have that space and it should consume only the actual character length. Please help with a solution for it.

Thanks & Regards,

Sri


Sponsored



Replies

Olaf Helper on Wed, 18 Mar 2015 08:37:58


e.g. There is a column 'FileName' which is of nchar(2000) and the actual file name size is 150 characters. So, it is taking total length by giving 150 characters for FileName and remaining length for space. 


That's the normal behaviour of the data type char/nchar to allocate the whole space and fill it with spaces. Use nvarchar instead; see MSDN nchar and nvarchar (Transact-SQL)

Visakh16 on Wed, 18 Mar 2015 09:50:07


Hi All,

Can anyone please help me resolving the below issue?

I have created a SQL table with multiple columns to which I am inserting data from an XML file via SSIS. When the data got inserted in to the table, the value of the column is using the total length of the field rather only required space.

e.g. There is a column 'FileName' which is of nchar(2000) and the actual file name size is 150 characters. So, it is taking total length by giving 150 characters for FileName and remaining length for space. 

I don't want to have that space and it should consume only the actual character length. Please help with a solution for it.

Thanks & Regards,

Sri

Thats the way char fields behave.

They always assume full space regardless of the actual contents present.

If you want it to assume only actual space use nvarchar or varchar instead

Latheesh NK on Wed, 18 Mar 2015 10:26:26


nchar is fixed length unicode datatype, that needs to be used to accommodate unicode  character along and suitable for fixed length data entries. nvarchar is variable length unicode datatype.

So if your data length is variable by nature, you may consider changing the datatype from nchar to nvarchar.