Urgent need for problem of increase in database size for ntext type fields

I had several databases under sqlserver ce 2.0 (in my Pocket PC) which contained ntext fields. The size of the databases varies from 50,000 to 700,000 records. The size of an ntext field is from 4 bytes to 2 megabytes.

When I recreated my databases under sql server 2005 mobile on my desktop using VS2005 (see my post just under this one), I saw a big difference between the old and new database sizes. This problem was mentioned in one of the posts in this forum and the reply was to replace ntext data with ncharvar type.

Since most of my data was longer than 4000 bytes (which is the limit for nvarchar type), I couldn't use this suggestion. Instead, I changed my ntext type to image type and used a GetByte conversion.

No change! The size of the new database is still 50 % larger than the original. Since the difference is around 300 MB, this is an unacceptable thing.

Now, I either wait from somebody to suggest a new solution (apart from keeping the ntext data in a separate binary file and keep index of the records of this file in the records of sql database) or, most preferably, have

Microsoft solve this problem as soon as possible.

Thanks for any help in advance

Talat

Answer this question

Urgent need for problem of increase in database size for ntext type fields

greenmind

The related story which you already mentioned is @ http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=169637&SiteID=1.

Here are the details:

SQL Mobile 3.0 reserves a data page for Long Value data when data length is more than 256 bytes. NTEXT and IMAGE are long value data types. So, if you have a table with NTEXT/IMAGE column, then SQL Mobile 3.0 creates a data page for each row where the data size is more than 256 bytes. And data page size is typically 4K . If you want to update the NTEXT/IMAGE column, the operation will be very fast and it is by design. Also, whenever there is a data length exceeding 4K (not really 4K but 4K minus some control data size), another data page is allocated. Even if your data value is just 4.1K, 8K is what reserved by SQL Mobile 3.0. Best practice here would be to align your data sizes on 4K boundary.

Note: Page size may not be 4k always. It varies!

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Mobile, Microsoft Corporation
Duie

I understand your scenario. But I am sorry that SQL Mobile 3.0 can not help you in this regard. However, we would consider this as a customer pain point and would discuss with the team.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation
huahsin68
Thank you very much for your valuable explanation. Let me ask another question:

If I will never update these long value data, and I want my database as compact as possible (such as in SqlServerCE) what can I do Is there a solution to my problem, or shall I stick to the design and have an increase in size of my databases

Talat

PS: In the other post you have mentioned, Darren Shaffer said:

"...sent the sample code to recreate this to the SQL Mobile team and will followup with them on this issue and post the results here...."

Hence I think you'd better post this message to the other forum to make the others' mind clear.
Urgent need for problem of increase in database size for ntext type fields

Answer this question