Microsoft KB Archive/914827

= An .sdf database file in SQL Server 2005 Compact Edition 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 =

Article ID: 914827

Article Last Modified on 12/6/2006

-

APPLIES TO


 * Microsoft SQL Server 2005 Compact Edition

-





SYMPTOMS
When you use the ntext data type or the image data type, an .sdf database file in Microsoft SQL Server 2005 Compact Edition 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 2005 Compact Edition.
 * 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 2005 Compact Edition 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 2005 Compact Edition 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 2005 Compact Edition 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 2005 Compact Edition 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.

Keywords: kbexpertiseadvanced kbtshoot kbprb KB914827

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.