Converting nvarchar(4000) to nvarchar(max)

Question

We have a few tables in our SQL Server (2005 & 2008) database with columns defined as nvarchar(4000). We sometimes need to be able to store more data than that and consider to use nvarchar(max) instead. Now to the questions.

1. Are there any performance implications we should know of?
2. Is it safe to use "alter table" to do the actual migration or is there another way?
3. Anything else we should know before we convert?

Regards Johan

Answer 1

We've had to do this as well in a few spots

1. We didn't see any performance hits, its been 5 weeks since the change
2. 'Alter table' is fine as long as the table is not getting written to during the conversion
3. Be sure to have a backup just in case and depending on the amount data in the table it could take a while