Search code examples
sql-serverdatabaseunicodealter-tablenvarchar

Varchar to nvarchar without changing length

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)

enter image description here

Solution

  • 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.