I expect this post will be of help to you.
- 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
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/