Estimating Database Size

Estimating database size before you create a database will help you with database planning.

When you create a database, it is important to accurately specify the amount of space to allocate for it. If you allocate too much space, you waste device space that could be used by other databases. If you allocate too little space, the database may run out of storage space. Although you will be able to easily expand the room allocated to the database at a later date, as long as there is space available on one or more devices, it is recommended that you estimate your database size before you begin.

Note Although you can perform detailed space calculations, there is still a chance that you will run out of space due to fragmentation, page splits, log overhead, and other factors. For example, in some situations log:data overhead can be 10:1. This means that updating 50 bytes of data can require 500 bytes of overhead. Space calculations can be very difficult to perform with accuracy, and it is recommended that you weigh the time cost of calculating space against the uncertainty of any benefit.

This section will show you how to:

Calculate the size of a table with a clustered index.
Calculate the size of a table with a nonclustered index.

The following examples are used:

Calculating the size of a 9,000,000-row table with a clustered index
Calculating the size of a 9,000,000-row table with a nonclustered index

Since the amount of overhead in tables that contain variable-length fields is greater than the overhead in tables containing fixed-length fields, two sets of formulas are presented in this 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.

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 FILLFACTOR in your CREATE INDEX statement, it will change some of the equations. For more information, see "Other Factors" later in this article. If a table includes text or image data types, use 16 bytes—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 article.

The following table shows the storage sizes for SQL Server data types.

Data type	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–23), 8 (for precision 24–53)
double precision	8
real	4
money	8
smallmoney	4
datetime	8
smalldatetime	4
bit	1
decimal	2–17 bytes, depending on precision
numeric	2–17 bytes, depending on precision
text	16 bytes + 2K per initialized column, or data size + text/image overhead, whichever is larger
image	16 bytes + 2K per initialized column, or data size + text/image overhead, whichever is larger
timestamp	8

Note Any columns defined to accept null values must be considered variable-length columns, since they involve the overhead or space savings associated with the variable-length columns and are stored as variable length.

The decimal and numeric data types 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 data type. The following table shows the mapping from precision to size.

Precision	Size

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 article.