Many people use databases, do not know how to calculate table space size, the effective calculation table space can effectively improve efficiency and understand the storage of tables to achieve a better control table. The calculation method of table space is shown below for reference.
Data type size
CHAR / BINARY specifies the size specified in the definition
VARCHAR / VARBINARY actual data size, uses an average estimate
INT 4 bytes
Smallint 2 byte
Tinyint 1 byte
Float 8 bytes
Float (b) 4 bytes (accurate to one to seven decimal numbers)
FLOAT (C) 8 bytes (accurate numbers to eight to fifteen decimals)
Double Precision 8 bytes
Real 4 bytes
Money 8 bytes
Small Money 4 bytes
DateTime 8 bytes
SmallDatetime 4 bytes
Bit 1 byte
DECIMAL / NUMERIC According to the accuracy, 2-17 bytes
TEXT / Image Each table is 16 bytes, each NOT NULL list, plus 2KB pages
TimeStamp 8 bytes
Allow columns of NULLs to be defined as VAR data types
Numerical precision
0-9 5 bytes
10-19 9 bytes
20-28 13 bytes
29-38 17 bytes
1. Calculation method with cluster index table
Data length calculation method:
. Row size = 2 (with the size of the column calculated by byte)
. Row size = 2 (total value of the fixed length column of byte) (a variable length column of the variable length column) (the number of variable length columns) (number of variable length columns) 2
. The number of rows in a page = 8060 / (the size of the line) (only got home)
. Number of 8KB data pages = (number of rows required) / (number of rows per page) (only gone)
Cluster index length method:
. Row size = 5 (with the size of the column calculated by byte)
. Row size = 5 (total value of the fixed length column of byte) (the number of variable length columns of the variable length column) (the number of variable length columns 1) 2
. The number of rows in a page = 8060 / (line size) -2
. Number of index pages at N level = (number of data pages) / (number of cluster indexes on a page)
Example: There is a table, there is an int cluster index, estimated the space required for 5,000,000 rows, assumes that there are four 23-byte long VAR type columns, and the average length of the column is 60% of the defined length. . 2 4 (92 * 60%) (4 1) 2 = 63
8060/63 = 127
5000000/127 = 39371
5 4 = 9
8060 / 9-2 = 894
39371/894 = 45
45/894 = 1
The total number of 8KB pages required for database tables:
Data Page: 39371
Index Page (Level 1): 1
Index Page (Level 0): 45
Total Number of 8KB Pages: 39417 (OR ABOUT 308MB)
2. Calculation method with non-clustered index
. Index line size = 7 (total value of fixed length keyword)
. Leaf Search Radio Size = 9 (Total Value of Fixed Length Keywords) (Total Value of Variable Length Keywords) (Variable Length Keyword) 1
. The number of leaves per page = 8060 / (leaf index) (only gone)
. Leaf page number = (number of rows in the table) / (number of leaves per page) (only to resembling)
. Non-leaved line size = leaf index quantity 4
. Number of non-female indexes per page = 8060 / (non-leaf line size) -2
. Number of index pages on n-level = (number of leaves pages) / (number of non-leaf indexes per page) (only to 不))
Example: There is a table, there is a six-way non-clustered index, estimated the space required for 5,000,000 rows.
7 6 = 13
8060/13 = 620
5000000/620 = 8065
13 4 = 17
8060 / 17-2 = 473
8065/473 = 18
18/473 = 1
The total number of 8kb pages required for data sheets:
Leaf Page: 8065
Leaf Index Page (Level 1): 1
Leaf Index Page (Level 0): 18
Total Number of 8KB Pages: 8084 OR ABOUT 64MB
