Article ID: 827968
Article Last Modified on 11/20/2007
APPLIES TO
- Microsoft SQL Server 2000 Windows CE Edition 2.0
- Microsoft SQL Server 2000 Windows CE Edition 1.1
- Microsoft Encarta Interactive World Atlas 2001
- Microsoft SQL Server 2005 Compact Edition
SUMMARY
This article describes how to estimate the size of a Microsoft SQL Server 2000 Windows CE Edition database or a Microsoft SQL Server 2005 Mobile Edition database. However, this estimate cannot be determined with real accuracy because of the following reasons:
- The size of the system table may vary, especially when you use SQL Server CE or SQL Server 2005 Mobile Edition synchronization.
- The estimate does not account for the space that is taken by indexes.
- For simplicity, the estimate assumes contiguous row storage (that is, after you compact a database). SQL Server CE or SQL Server 2005 Mobile Edition stores rows on 4 KB pages, and it does not split rows across pages, except for text and ntext data types. Because all the pages are not always filled, some unused space exists on individual pages. Also, if a page has rows and some of those rows are deleted, the space for the deleted rows is not reclaimed until the database is compacted. If all the rows in a page are deleted, the page is reclaimed later.
- The length of some data types may vary depending on the data that they contain, including the nvarchar and ntext data types. When you estimate the size of a column, the estimate is based on the maximum column size, not on the actual size of the data that is stored in the column. For example, if a column has a data type of nvarchar(50) and has a value of "Hello," the actual size of the column is 5 bytes, but the estimated size is 50 bytes.
MORE INFORMATION
When you estimate the size of a table or of a database, consider the following information:
- Each row in the table has an overhead of 6 bytes.
- Each column in the table has an overhead of 1 byte, plus 1 byte for every 256 bytes of row storage.
- Fixed-length data types have an overhead of 1 byte for each column, and the overhead is rounded to the next higher byte.
- Zero-length string columns occupy 1 byte in the row.
- SQL Server CE or SQL Server 2005 Mobile Edition stores data in Unicode format. If the collation string that is used to create the database is a double-byte character set (DBCS) language (such as kanji), the size of the string columns is doubled because each byte is stored as 2 bytes. If the collation string that is used to create the database is a single-byte character set (SBCS) language (such as English), SQL Server CE removes the leading unused byte. As a result, SBCS collation strings are estimated as 1 byte for each character.
- If the SQL Server CE tables or the SQL Server 2005 Mobile Edition tables are used in merge replication, there are three system columns for each table.
- If the SQL Server CE tables or the SQL Server 2005 Mobile Edition tables participate in Remote Data Access (RDA), there are 2 system columns for each table.
The following table describes the data types in SQL Server CE and the storage size for each data type.
| Data type | Description |
|---|---|
| bigint | Integer (whole number) data from -2^63 (- 9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807). The storage size is 8 bytes. |
| integer | Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). The storage size is 4 bytes. |
| smallint | Integer data from –32,768 to 32,767. The storage size is 2 bytes. |
| tinyint | Integer data from 0 to 255. The storage size is 1 byte. |
| bit | Integer data with either a 1 or 0 value. |
| numeric (p, s) | Fixed-precision and scale-numeric data from -10^38 +1 through 10^38 ?1. The p specifies precision and can vary between 1 and 38. The s specifies scale and can vary between 0 and p. Numeric always uses 19 bytes, regardless of its precision and scale. |
| money | Monetary data values from -2^63 (- 922,337,203,685,477.5808) through 2^63 - 1 (922,337,203,685,477.5807), with an accuracy to a ten-thousandth of a monetary unit. The storage size is 8 bytes. |
| float | Floating point number data from -1.79E + 308 through 1.79E + 308 The storage size is 8 bytes. |
| real | Floating precision number data from -3.40E + 38 through 3.40E + 38. |
| datetime | Date and time data from January 1, 1753 to December 31, 9999 with an accuracy of one three-hundredth of one second or 3.33 milliseconds. Values are rounded to increments of .000, .003 or .007 milliseconds. Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date (January 1, 1900). The base date is the system's reference date. Values for datetime that are earlier than January 1, 1753 are not permitted. The other 4 bytes store the time of day as the number of milliseconds after midnight. Seconds have a valid range from 0 through 59. |
| national character(n) Synonym: nchar(n) | Fixed-length Unicode data with a maximum length of 255 characters. The default length is 1. The storage size, in bytes, is two times the number of characters entered. |
| national character varying(n) Synonym: nvarchar(n) | Variable-length Unicode data with a length of 1 to 255 characters. The default length is 1. The storage size, in bytes, is two times the number of characters entered. |
| ntext | Variable-length Unicode data with a maximum length of (2^30 - 2) / 2 (536,870,911) characters. The storage size, in bytes, is two times the number of characters entered. |
| binary(n) | Fixed-length binary data with a maximum length of 510 bytes. The default length is 1. |
| varbinary(n) | Variable-length binary data with a maximum length of 510 bytes. The default length is 1. |
| image | Variable-length binary data with a maximum length of 2^30 – 1 (1,073,741,823) bytes. |
| uniqueidentifier | A GUID. Storage size is 16 bytes. |
| IDENTITY [(s, i)] | This is a property of a data column, not a distinct data type. Only data columns of the integer data types can be used for identity columns. A table can have only one identity column. A seed and increment can be specified, and the column cannot be updated. s (seed) = starting value i (increment) = increment value. |
| ROWGUIDCOL | This is a property of a data column, not a distinct data type. This is a column in a table that is defined by using the uniqueidentifier data type. A table can only have one ROWGUIDCOL column. |
If you are using SQL Server 2005 Mobile Edition
For more infomation about the data types in SQL Server 2005 Mobile Edition, see the "Data Types" topic in SQL Server 2005 Books Online.
In the following scenarios, the size of a table is estimated depending on the data type of the column, the number of rows, the collation, and the type of synchronization.
Scenario 1
In this scenario, the size of the myTableA1 table is estimated. The table has the following characteristics:
- The myTableA1 table has 10,000 rows.
- The myTableA1 table is created with the default English collation, and the table does not use any form of SQL Server CE synchronization.
The following table describes the type of each column in the myTableA1 table and the number of bytes that are occupied by each column.
| Column | Data Type | Size in Bytes | Overhead bytes |
|---|---|---|---|
| 1 | nvarchar | 25 | 1 |
| 2 | nchar | 25 | 1 |
| 3 | bigint | 8 | 1 |
| 4 | datetime | 8 | 1 |
| n/a | Overhead | n/a | 6 |
| Totals | 66 | 10 |
The total size of each row in the myTableA1 table is 76 (66+10) bytes. Because the table has 10,000 rows, the total size of the myTableA1 table is 760,000 bytes (76*10,000). Because the calculation takes into account the maximum size of the nvarchar data type, this is only an estimate of the table size. The actual size of the table may vary depending on the value that is stored in the nvarchar data type.
Note If the data values in a column are expected to vary considerably, you must use the nvarchar data type to reduce the database size.
Scenario 2
In this scenario, the size of the myTableA2 table is estimated. The table has the following characteristics:
- The myTableA2 table has 91 rows.
- The myTableA2 table is created with the default English collation, and the table is used in merge replication.
The following table describes the type of each column in the myTableA2 table and the number of bytes that are occupied by each column.
| Column | Data Type | Size in Bytes | Overhead bytes |
|---|---|---|---|
| CustomerID | nvarchar | 5 | 1 |
| CompanyName | nvarchar | 40 | 1 |
| ContactName | nvarchar | 30 | 1 |
| ContactTitle | nvarchar | 30 | 1 |
| Address | nvarchar | 60 | 1 |
| City | nvarchar | 15 | 1 |
| Region | nvarchar | 15 | 1 |
| PostalCode | nvarchar | 10 | 1 |
| Country | nvarchar | 15 | 1 |
| Phone | nvarchar | 24 | 1 |
| Fax | nvarchar | 24 | 1 |
| n/a | Row Overhead | n/a | 6 |
| n/a | Overhead for greater than 256 bytes | n/a | 1 |
| n/a | Integer for replication | 4 | 1 |
| n/a | UniqueIdentifier for replication | 16 | 1 |
| n/a | Binary | 24 | 1 |
| Totals | 312 | 21 |
The total size of each row of the myTableA2 table is 333 (312+21) bytes. Because the table has 91 rows, the total size of the myTableA2 table is 30,303 bytes. Because the actual size of the string column values is not known, this size is only an estimate.
Scenario 3
In this scenario, the size of the myTableA3 table is estimated. The table participates in RDA and has the following characteristics:
- The myTableA3 table has 91 rows.
- The myTableA3 table is created with the default English collation, and this table has the primary key columns because they are used as part of one of the system columns that is tracked for RDA.
The following table describes the type of each column in the myTableA3 table and the number of bytes that are occupied by each column.
| Column | Data Type | Size in Bytes | Overhead bytes |
|---|---|---|---|
| CustomerID (PK) | nvarchar | 5 | 1 |
| CompanyName (PK) | nvarchar | 40 | 1 |
| ContactName | nvarchar | 30 | 1 |
| ContactTitle | nvarchar | 30 | 1 |
| Address | nvarchar | 60 | 1 |
| City | nvarchar | 15 | 1 |
| Region | nvarchar | 15 | 1 |
| PostalCode | nvarchar | 10 | 1 |
| Country | nvarchar | 15 | 1 |
| Phone | nvarchar | 24 | 1 |
| Fax | nvarchar | 24 | 1 |
| n/a | Row Overhead | n/a | 6 |
| n/a | Overhead for greater than 256 bytes | n/a | 1 |
| n/a | Integer for RDA | 4 | 1 |
| n/a | Binary for RDA (4 bytes for each column in the primary key columns, plus bytes of the column value if the primary key is changed; otherwise, 1 byte) | 53/1 | |
| Totals | 325/273 | 20 |
If the primary key is changed, the total size of the table is 345 (325+20) bytes for each row. Because the myTableA3 table has 91 rows, the size of the table is 31,395 bytes. If the primary key is not changed, the total size of the table is 293 (273+20) bytes for each row. Because the myTableA3 table has 91 rows, the size of the table is 26,663 bytes. Because the actual size of the string column values is not known, this is only an estimate.
Keywords: kbtable kbdatabase kbinfo KB827968
