8

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 ?

Improve this question
  • 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 '13 at 11:14
  • 2
    Is the database larger or is the log very large? – Aaron Bertrand Mar 6 '13 at 12:13
  • Sorry Marian, understand now... – Aidan Lawless Mar 6 '13 at 12:35
  • Aaron, the MDF file is much larger....almost 10GB larger – Aidan Lawless Mar 6 '13 at 12:36
7

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

  • 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/

Improve this answer
  • 2
    could you sum up here the important facts, in case the link may become dead. – Stephane Rolland Mar 6 '13 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 '13 at 12:15
  • Thanks... I have been putting off joining for years now... Glad to be here. – RoKa Mar 6 '13 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..... – Aidan Lawless Mar 6 '13 at 14:07
  • It is my pleasure. – RoKa Mar 6 '13 at 16:24
6

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).

Improve this answer
  • Yes, thats what i'm doing ... although based on some simple calculations it wouldnt account for the size of the increase. – Aidan Lawless Mar 6 '13 at 12:37
  • 2
    Have you tried with RoKa's suggestion? – spaghettidba Mar 6 '13 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 '13 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. – Aaron Bertrand Mar 6 '13 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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