- Calculation for determining the number of 2K pages needed for a
table with no indexes or text/image columns:
The overhead per data row is roughly 7 bytes plus the number of
variable-length and/or nulls-allowed fields per row. The formula
is as follows
Row length = 7 bytes + (data length) + (#var or null fields)
where:
data length = is the sum of the column lengths.
#var or null fields = 1 byte is counted for every data element
defined as varchar or able to accept
null values.
Consider the following table definition:
first_element char(2) null,
second_element varchar(10),
third_element char(3),
fourth_element varchar(20) null
row length = 7 + (35) + (3)
row length = 45
NOTE: When the datatype varchar is defined to accept NULLs, only
1 byte is counted for this condition.
Because rows must fit into 2K pages, where each page has 32 bytes
of overhead, The number of pages required to hold a table is
defined with the following formula:
pages = [number of rows] / (2016/(row length))
For example,
(800000 rows) / (2016/(100 bytes per row)) = ~40000 pages
-or-
pages = [number of rows] * ((row length)/2016)
For example,
(800000 rows) * ((100 bytes per row)/2016)= ~40000 pages
- Considerations for tables with text/image columns:
When a table has a text/image column, 16 bytes are used for managing
the text/image column (4 bytes are used for a page pointer, 8 bytes
for a timestamp, and 4 bytes for other management functions). One
page (2K) is reserved for each text/image column in a row.
- Considerations for page management:
There is a page-management page for every 255 user pages. Each page
is 2048 (2016 + 32 bytes for overhead); therefore, this amounts to
two page-management pages per megabyte with 4K of page-management
overhead per megabyte.
Another consideration for the page-management technique is that a
maximum 32 distinct objects can be allocated per page-management
page. Each index on a table counts as an additional object. For
example: if 8 tables, each with 3 indexes, were created, 1/2
megabyte would be reserved, even if no data were in the table:
(3 indexes + table) * 8 = 32 objects.
- Considerations for indexes on tables:
Indexes also consume pages; the value being indexed is replicated
in each row. The formulas for calculating the page requirement is
the same as for data.
The overhead per clustered index row is roughly 8 bytes plus the
number of variable-length/nulls-allowed fields per row. The formula
is defined as:
row length = 8 + (data length) + (#var or null fields)
The overhead per non-clustered index row is roughly 12 bytes plus
the number of variable-length/nulls-allowed fields per row. The
formula is defined as:
row length = 12 + (data length) + (#var or null fields)
The number of pages in an index depends on how many index rows fit
in a page, and this is determined the same way as for data. The
formula is defined as:
pages = [number of rows] / (2016/(row length))
For example,
(800000 rows) / (2016/(100 bytes per row)) = ~40000 pages
-or-
pages = [number of rows] * ((row length)/2016)
For example,
(800000 rows) * ((100 bytes per row)/2016)= ~40000 pages
Please keep in mind that these formulas will give you a rough
estimate on space required for your database.
For more information on this topic, see Appendix A of the "Microsoft
SQL Server System Administration Guide" for version 4.2.