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 ?

  • 2
    Oh, don't create the same question here. Just flag it on SO and mods will migrate it here in an instant :-). Now somebody needs to merge/clean..etc.
    – Marian
    Mar 6, 2013 at 11:14
  • 2
    Is the database larger or is the log very large? Mar 6, 2013 at 12:13
  • Sorry Marian, understand now... Mar 6, 2013 at 12:35
  • Aaron, the MDF file is much larger....almost 10GB larger Mar 6, 2013 at 12:36

2 Answers 2


I expect this post will be of help to you.


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/

  • 2
    could you sum up here the important facts, in case the link may become dead. Mar 6, 2013 at 11:38
  • As mentioned in other answers, TEXT vs. NTEXT will have an impact on the storage, but not by as much as you have suggested.
    – RoKa
    Mar 6, 2013 at 12:15
  • Thanks... I have been putting off joining for years now... Glad to be here.
    – RoKa
    Mar 6, 2013 at 12:27
  • Thanks RoKA, that has made a significant difference to a couple of the tables in question. I'm going to restore the database again and do it incrementally to see the affects. Thanks again..... Mar 6, 2013 at 14:07
  • It is my pleasure.
    – RoKa
    Mar 6, 2013 at 16:24

Might be an oversight in your question, but you're saying TEXT to NVARCHAR(max) and not NTEXT to NVARCHAR(max). If this is what you're really doing, you're changing from ANSI to UNICODE and you shouldn't be surprised it takes more space (single byte characters vs. multi-byte characters).

  • Yes, thats what i'm doing ... although based on some simple calculations it wouldnt account for the size of the increase. Mar 6, 2013 at 12:37
  • 2
    Have you tried with RoKa's suggestion? Mar 6, 2013 at 12:41
  • 1
    @AidanLawless what calculations are these? The size of the database should increase at least by the size of the existing TEXT field - going to unicode takes exactly twice the storage space of ANSI.
    – JNK
    Mar 6, 2013 at 13:25
  • 2
    @JNK ... unless using compression in 2008 R2 or better, where Unicode compression will treat ASCII characters stored in NVARCHAR like VARCHAR. Mar 6, 2013 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.