Converting nvarchar(4000) to nvarchar(max)

Question

Hi!

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

Answer 2

If the text does not fit on the page with the rest of the data, the text will be stored in another page. This causes an extra database page read. So performance can/will be slower. On systems with a heavy load, and a very restricted response time, maybe.

The SQL Server Management studio will probably rename the current table, create a new one with the new layout, copy data, remove old table. This can be slow.

I don't know if the alter table will work.