SQL SERVER 2005 nvarchar(max) Alter Table Alter Column ntext

SQL SERVER 2005 converting ntext Column Data Type to nvarchar(max) dataType will shrink your SQL SERVER TABLE SIZE BY 50%....and improve performance too:

LINK1 (I posted the information on this page...to the LINK1)http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

LINK2>

http://decipherinfosys.wordpress.com/2007/07/24/update-textntext-or-varcharmax-data-type-columns-in-sql-server/

Link3>

http://blog.sqlauthority.com/2007/05/26/sql-server-2005-replace-text-with-varcharmax-stop-using-text-ntext-image-data-types/

Link4>

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9cd2b083-d2fb-42dd-a37e-ab3b93ed1f73/

Here is the information I posted to the blog above abot the disk space savings you acquire once you have converted and udpated the data in these colomns:

Foot Print Size on DISK after converting to NVARCHAR(MAX)

I have a a table which is 14 GIG when using (ntext).

After I convert this table to varchar(max), will will the footprint on the hard disk look like.

Will it be 1/2 the size to be about 7 GB?
or
Will it be the same size of 14GB?
or
Will it actually become bigger 21GB?

I choose to not recreate the table, since I had other idenity columns which I would have to turn
set identity off //on.

So I used alter table / alter column signature varchar(max).

In my first tests...it appears the table is actually increasing in size to be more than 21 GB in size? Not what I was anticpating...

Anynow, if someone could help me answer why this may happen the(table size actually increasing by 1/3)?

I even ran a backup and shrank the database to reclaim additional space (if any existed)...But the only thing that shrank was the log file (ldf).
to note: I had to set the log file size to be 16 GIG when originally performing the task becuase it said the log file was not large enough, even though I had set the log file to autogrow by 5%. This could be related to some other task running too....but expanding the log file before running alter table/alter column statement seem to at least fix that part.

--Still working on this situation...My goal was to get with the "new" standard of varchar(max) before looking into sql 2008 and to hopefully also shrink the size of our overall database/backups.

Any help would be appreciated.
Thanks, Doug Lubey of Louisiana

3/7/2009 10:10 AM

update to foot print size of varchar(max) ....RESULTS

Just like this article suggests...
After I altered the data column type to varchar(max) the table will EVENTUALLY SHRINK TO BE 1/2 THE SIZE..if you follow your alter table statement with and UPDATE TABLE STATEMENT.

ORIGINALLY WITH JUST RUNNING THE ALTER TABLE:
The table size did double....why? Behind the scenes SQL server 2005 actually creates a duplicate feild of the same data. One field with the data type "text" and one field with the datatype varchar(max).

BASED ON THE SUGGESTION ABOVE:
I then ran an update statement

UPDATE StSignaturesTbl
set signatureString=signatureString,
where Date_Created >= '1/1/2002'
================
At first the dataTable did not shrink even after running this statement...but slowly it did..after checking back on a hourly basis.

HERE IS THE OUTLINE OF WHAT HAPPENED:
-----
1>original table size: 14 GB
2>Alter Table/Alter Column: 21 GB (it increased)
3>Waited....it stayed the same ...21 gigabytes
4>Ran the update statement(Above)..no changes at first
5>Waited ....3-6 hours...the table eventually shrank to be 1/2 the size of the original foot print size on the hard disk..

END RESULT: After both tasks completed and after waiting patiently between tasks. THE FINAL SIZE FO THE DATATABLE IS NOW 7 GB in size....

THIS IS WHAT WE WERE EXPECTED...50% gain....in hard disk space..

Thanks again for this article...
Doug Lubey of Louisiana

ONE THING TO NOTE: make sure you ADD enough space to your log file (.ldf) to cover an additional 100% of the TABLE SIZE...before running the alter column/alter table statements or you will get a run-time error once the log file size reaches it upper limit (EVEN IF YOU have auto-increase set to 10%)...I Did have it said to increase automatically by 10%...SQL SERVER 2005 still said it could not reclaim disk space fast enough ..do not know why...but increasing the log file size to the original size + THE table size did the trick. USED THE properties(right-click_) of the database to do this. 3/8/2009 4:17 PM

Doug Lubey and Family

Foot Print Size on DISK after converting to NVARCHAR(MAX)

update to foot print size of varchar(max) ....RESULTS