This appendix contains formulas and examples that show how to estimate the size of tables and indexes in a database.
The following examples are used:
Since the amount of overhead in tables containing variable-length fields is greater, two sets of formulas are presented in each example ¾ one for fixed-length fields and one for variable-length fields.
To calculate the size of a database, add the number of bytes of data and associated overhead and divide that number by the number of bytes available on a data page. Each 2K data page uses 32 bytes of overhead, so there are 2016 (2048 - 32) bytes available for data on each data page.
For best accuracy, round down divisions that calculate the number of rows per page, and round up divisions that calculate the number of pages.
If you are using FILL FACTOR in your CREATE INDEX statement, it will change some of the equations. For more information, see Other Factors, later in this appendix.
If a table includes text or image datatypes, use 16K (the size of the text pointer that is stored in the row) as indicated in the following examples, and see Using Average Sizes for text/image Data Pages, later in this appendix.
The following are the storage sizes for SQL Server datatypes:
| Datatype | Size |
|---|---|
| char | Defined size |
| varchar | Data size |
| binary | Defined size |
| varbinary | Data size |
| int | 4 |
| smallint | 2 |
| tinyint | 1 |
| float | 8 |
| float(b) | 4 (for precision of 1-7), 8 (for precision of 8-15) |
| double precision | 8 |
| real | 4 |
| money | 8 |
| smallmoney | 4 |
| datetime | 8 |
| smalldatetime | 4 |
| bit | 1 |
| decimal | 2-17 bytes, depending on precision (see the following table) |
| numeric | 2-17 bytes, depending on precision (see the following table) |
| text | 16 bytes + 2K per initialized column |
| image | 16 bytes + 2K per initialized column |
| timestamp | 8 |
Note Any columns defined to accept NULL values must be considered variable-length columns, since they involve the overhead associated with the variable-length columns.
The decimal and numeric datatypes have a maximum precision of 38. Based on the precision specified, a length is computed and used as the size of the array to store the datatype. The following table shows the mapping from precision to length.
| Precision | Size (bytes) |
|---|---|
| 0 - 2 | 2 |
| 3 - 4 | 3 |
| 5 - 7 | 4 |
| 8 - 9 | 5 |
| 10 - 12 | 6 |
| 13 - 14 | 7 |
| 15 - 16 | 8 |
| 17 - 19 | 9 |
| 20 - 21 | 10 |
| 22 - 24 | 11 |
| 25 - 26 | 12 |
| 27 - 28 | 13 |
| 29 - 31 | 14 |
| 32 - 33 | 15 |
| 34 - 36 | 16 |
| 37 - 38 | 17 |
All calculations in the following examples are based on the maximum size for varchar and varbinary data ¾ the defined size of the columns. They also assume that columns were defined as NOT NULL. If you want to use average values for variable-length columns, see Using Average Sizes for Variable Fields, later in this appendix.