An .sdf database file in SQL Server Mobile is larger than the same .sdf database file in SQL Server CE 2.0 when you use the ntext data type or the image data type (914827)


The information in this article applies to:

  • Microsoft SQL Server 2005 Mobile Edition

SYMPTOMS

When you use the ntext data type or the image data type, an .sdf database file in Microsoft SQL Server 2005 Mobile Edition (SQL Server Mobile) is larger than the same .sdf database file in Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) 2.0.

This behavior may occur when the following conditions are true:
  • You upgrade a database from SQL Server CE 2.0 to SQL Server Mobile.
  • The database contains data of the ntext data type or of the image data type.
Note The ntext data type and the image data type are frequently called the binary large object (BLOB) data types.

CAUSE

This behavior occurs because of a change in the way that SQL Server Mobile allocates space to deal with the binary large object data types. When the size of the binary large object data exceeds 256 bytes, SQL Server Mobile puts all the binary large object data into a new data page. The size of the new data page is 4 kilobytes (KB). Therefore, even if the size of the binary large object data is 257 bytes, SQL Server Mobile allocates 4 KB of space to the binary large object data. Although the binary large object data may not use all the space in the new data page immediately, 4 KB is occupied. When the size of the binary large object data exceeds 4 KB, SQL Server Mobile allocates a second data page, and a total of 8 KB is occupied.

WORKAROUND

To work around this behavior, use the nvarchar data type or the varbinary data type if you expect the data entries in a column of a table to be less than 4000 characters long.

STATUS

This behavior is by design.

MORE INFORMATION

You can compact the database to save space. However, the compact operation works at the page level. Therefore, the compact operation is not helpful in this situation.
Modification Type:MajorLast Reviewed:3/8/2006
Keywords:kbExpertiseAdvanced kbtshoot kbprb KB914827 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.