Pages

Sunday, January 31, 2010

How to calculate SQL table sizes?

Number of rows in the table = Num_Rows
Number of columns = Num_Cols
Sum of bytes in all fixed-length columns = Fixed_Data_Size
Number of variable-length columns = Num_Variable_Cols
Maximum size of all variable-length columns = Max_Var_Size

Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )

Total size of variable-length columns (Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) + Max_Var_Size

Total row size (Row_Size) = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4

Number of rows per page (Rows_Per_Page) = ( 8096 ) / (Row_Size + 2)

Number of free rows per page (Free_Rows_Per_Page) = 8096 x ((100 - Fill_Factor) / 100) / (Row_Size + 2)

Number of pages (Num_Pages) = Num_Rows / (Rows_Per_Page - Free_Rows_Per_Page)

Table size (bytes) = 8192 x Num_Pages

No comments:

Post a Comment