How to Alter Column Data Type Without Affecting Existing Column Width

Question

Hi Folks,

I have an existing column of data type varchar that I need to change to nvarchar, however I do not want to alter the existing column width of (5).

If I use the following statement

ALTER TABLE MYTABLE ALTER COLUMN MYCOLUMN NVARCHAR (5) NOT NULL

I end up with a column of nvarchar data type, but with a column width of (10)!

If I try the following statement without specifying a column width

ALTER TABLE MYTABLE ALTER COLUMN MYCOLUMN NVARCHAR (5) NOT NULL

I then end up with an nvarchar column with a width of (2)

How can I simply change the column data type from varchar to nvarchar without affecting the existing column width?

Thank you!

Answer 1

NVARCHAR is a UNICODE UCS-2 type and will be twice as wide as VARCHAR, by design.

Please have a very close read of SQL Server Books Online to see the difference between SQL data types. I've included a link.