CODEWITHSUNDEEP
sql-server-2008
Blade3
Blade3

Reputation: 4380

Changing a data column from varchar(max) to nvarchar(max) in SQL Server 2008

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?

Upvotes: 13

Views: 39687

Answers (4)

Chris Wood
Chris Wood

Reputation: 575

As of SQL Server 2016, this can now be an ONLINE operation

SQL 2016 ALTER COLUMN online

Upvotes: 0

RThomas
RThomas

Reputation: 10880

Always take a backup so no matter what happens you can go back to the way things were.

With that said changing from varchar(max) to nvarchar(max) should not lose data.

Side note: you might have concerns if you were changing in the other direction nvarchar(max) to varchar(max) as the process would have to convert extended characters if any were present.

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280439

Another option to minimize disruption (since I don't believe the ALTER COLUMN will occur online), is:

ALTER TABLE dbo.table ADD new_column NVARCHAR(MAX); -- metadata operation

Now you can update the data in waves/batches/transactions instead of applying the change to the whole table in one long transaction. When you're satisfied that the data is copied over, you can drop the old column, rename the new one, and re-create any affected indexes (your MAX column can't be in the key of any index, but you might have inadvertently put it into an INCLUDE definition - if that is the case you should question that anyway).

You'll still want to rebuild all indexes to reclaim the now unused space (which you can do online if you're using Enterprise Edition).

Upvotes: 7

PinnyM
PinnyM

Reputation: 35531

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.

Upvotes: 17

Related Questions