Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 914827 - Last Review: March 27, 2008 - Revision: 3.2
An .sdf database file in SQL Server 2005 Compact Edition or in SQL Server 2005 Mobile 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
When you use the
ntext data type or the
image data type, an .sdf database file in Microsoft SQL Server 2005 Compact Edition or in Microsoft SQL Server 2005 Mobile 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 or to SQL Server 2005 Mobile 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.
This behavior occurs because of a change in the way that SQL Server 2005 Compact Edition or SQL Server 2005 Mobile 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 or SQL Server 2005 Mobile 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 or SQL Server 2005 Mobile 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 or SQL Server 2005 Mobile Edition allocates a second data page, and a total of 8 KB is occupied.
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.
This behavior is by design.
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.
APPLIES TO
- Microsoft SQL Server 2005 Compact Edition
- Microsoft SQL Server 2005 Mobile Edition
| kbexpertiseadvanced kbtshoot kbprb KB914827 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate