Menu
I have to change the data type in a SQL Server 2008 table from varchar(max) to nvarchar(max). This table has data in it. Is there a way to change the data type without losing the data?
307
nvarchar [ ( n | max ) ] max indicates that the maximum storage size is 2^30-1 characters (2 GB). The storage size is two times n bytes + 2 bytes. For UCS-2 encoding, the storage size is two times n bytes + 2 bytes and the number of characters that can be stored is also n.
The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.
You cannot create regular index on a VARCHAR(MAX) column. So, if you want a indexed string column then you have to go for VARCHAR(N). There is no direct way to restrict the length of the string stored in a VARCHAR(MAX) column. On the other hand, using VARCHAR(1-8,000), you can limit the string saved in the column.
You can just use standard syntax to alter the table:
ALTER TABLE some_table ALTER COLUMN some_column nvarchar(max)
You will probably want to rebuild any indices however, to make sure they work as expected.
92
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
