Web Wiz Forums - Access to SQL: 50% waste of DB size

Access to SQL: 50% waste of DB size

Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=16835
Printed Date: 15 April 2026 at 7:39pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Access to SQL: 50% waste of DB size

Posted By: madolik
Subject: Access to SQL: 50% waste of DB size
Date Posted: 07 October 2005 at 5:05am

After upsizing the Web Wiz Forum Access database to SQL I found a problem with the datatypes, most important in tblthread.

The upsizing assistant changes the tblthread.message datatype from memo in the Access database to ntext in the SQL version.

The original SQL database, when setting up with the Web Wiz SQL_setup script, has the field tblthread.message set to text, and not to ntext. The problem with that is that ntext is using twice as much space as text, so I would like to ask:

Can I change the SQL datatype of tblthread.message from ntext to text without losing data? I already tried it and the SQL database size was cut into half almost (From 70MB down to 38MB), but I would appreciate a technical explanation whether I am losing data that way.

Can some people here who upsized from Access to SQL please post the datatype of their field tblthread.message in the SQL database?

Thank you for your help.

Replies:

Posted By: madolik
Date Posted: 12 October 2005 at 7:19am

Topic was renamed and bumped since this should be of concern to everbody who upsized his Access database to SQL.

You are, in case this is not a problem with my upsizing assistant only, wasting 50% database space on your SQL server and you are very likely paying too much for SQL hosting. The difference between a 200MB SQL database and a 400MB SQL database can easily go into the $hundreds. Those 400MBs can be decreased to 200MB by simply setting the message field to text instead of ntext, that is how it worked for me!

Please post a reply when you know about any problems with the field type change from ntext to text. And I would still appreciate it when some users who upsized from Access to SQL would post the datatype of their field tblthread.message. Is it ntext or text?

Thank you!

Posted By: WebWiz-Bruce
Date Posted: 12 October 2005 at 8:07am

The following gives you some reasons:-

http://www.gotdotnet.com/Community/MessageBoard/Thread.aspx?id=341379 - http://www.gotdotnet.com/Community/MessageBoard/Thread.aspx?id=341379

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting