SQL Server alter table to change TEXT to NVARCHAR(MAX) greatly increasing database size

I need to update a SQL Server database thats about 18GB in size to change a significant number of TEXT columns to NVARCHAR(MAX).

The problem I'm having is after executing all the alter table commands the database ends up being almost 26GB in size. I understand that from here using NVARCHAR(MAX) will alow the DB to grow more slowly but is there any way for me to prevent this bloating ?

I expect this post will be of help to you.

http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

Key facts:

-
By default TEXT and NTEXT stores the text value in the LOB structure

-
By Default NVARCHAR(MAX) stores the text value in the table structure (Unless it is over 8000 bytes)

-
When you alter the column from the TEXT/NTEXT to NVARCHAR(MAX), the way the data is stored is not changed, it updates the table metadata only. The data structure is only changed the next time the value is changed. This can be done immediately by running something like this:

update mytable set mycolumn1 = mycolumn1
 where datalength(mycolumn1) <= 8000

-
If you use the default table option setting for NVARCHAR(MAX), then the data in your table will be larger.

• You will need to look at your table option settings and environment before changing the setting to what suits your needs.

• Your table size will eventually shrink, if you follow your alter table statement with the update table statement.

In short, if you run the update statement, forcing the storage of the data structure to be changed, your database size will be smaller, as expected.

EDIT: As you have mentioned TEXT and not NTEXT, your gain in space would be less obvious than you might think. NTEXT takes double the amount of space as what TEXT does, but at the same time, you should expect NVARCHAR(MAX) to take up around half the space as what NTEXT does. By my calculation, you would see little change from your original database size.

Special credit to http://www.douglubey.com/

StackExchange Database Administrators Q#36056, answer score: 7