Varchar to nvarchar without changing length

Question

I have this table that stores an id for each row in sub_id column of type varchar(255), sql server. Other columns store unicode thus those are nvarchar. Now I have this need to update sub_id to nvarchar for SOME reason. I ran following command:

 ALTER TABLE TABLE_NAME ALTER COLUMN SUB_ID NVARCHAR(255)

This changed the column type but set the length to 510. I do not want to change length of the column. If I must mention, table has a lot of data and length of sub_id never exceeds 20.

I read about this and could not figure out how to truncate the column in length.

Following is snapshot from sp_help on mentioned table (subject_id is sub_id)

I can't replicate your problem: [DB<>Fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=69f3752216979d40a0c382fb72e99556). — Thom A, Nov 06 '19 at 10:30
@Larnu sp_help shows increased column length, attached snapshot — pyroCoder, Nov 06 '19 at 10:33
@Larnu , may be he is telling about `CHARACTER_OCTET_LENGTH' — Arun Palanisamy, Nov 06 '19 at 10:34
@pyroCoder , You can alter by specifying `NVARCHAR(127)` which will have a `DATALENGTH` of `254` — Arun Palanisamy, Nov 06 '19 at 10:37

score 2 · Answer 1 · answered Nov 06 '19 at 10:34

The size of NVARCHAR is 255 and it Occupies 2 bytes of space for each character, that's the reason it showing DATALENGTH as 510

DECLARE @string NVARCHAR(20)  
SET @string = 'Robin'  
SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len'

As in the output above, you will observe DATALENGTH column is showing only 10 as a value. That is because it occupies 2 bytes of space for each character and the data length is only 5 characters, therefore it will occupy 10 bytes of space in the database.

Thanks , this was very useful. – pyroCoder Nov 19 '19 at 10:24 — pyroCoder, Nov 19 '19 at 10:24

Thom A · Accepted Answer · 2020-11-02T17:58:41.187

2

When you are looking at sp_help, length means the following (sp_help (Transact-SQL)):

Length smallint Physical length of the data type (in bytes).

It does not mean the length of the column in characters.

A single nvarchar character is 2 bytes in size, not one. The maximum length of the column hasn't changed, but its size in bytes has.

If you want the Data size of the column to remain the same, you would have to make the column an nvarchar(127)/nvarchar(128) (which would be 254/256 bytes in size), and would truncate your values.

edited Nov 02 '20 at 17:58

answered Nov 06 '19 at 10:37

Thom A

88,727
11
45
75

I tried 'select from INFORMATION_SCHEMA.COLUMNS ....' which gives me 255 only. Thanks. Limited understanding of sp_help lead to the confusion. – pyroCoder Nov 06 '19 at 10:43

Varchar to nvarchar without changing length

2 Answers2