×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

What I found for SQL Server online help....

本文发表在 rolia.net 枫下论坛Estimating the Size of a Table
The following steps can be used to estimate the amount of space required to store the data in a table:

Specify the number of rows present in the table:
Number of rows in the table = Num_Rows

If there are fixed-length and variable-length columns in the table definition, calculate the space that each of these groups of columns occupies within the data row. The size of a column depends on the data type and length specification. For more information, see Data Types.
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

If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )

Only the integer portion of the above expression should be used; discard any remainder.

If there are variable-length columns in the table, determine how much space is used to store the columns within the row:
Total size of variable-length columns (Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) + Max_Var_Size

If there are no variable-length columns, set Variable_Data_Size to 0.

This formula assumes that all variable-length columns are 100 percent full. If you anticipate that a lower percentage of the variable-length column storage space will be used, you can adjust the result by that percentage to yield a more accurate estimate of the overall table size.

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

The final value of 4 represents the data row header.

Calculate the number of rows per page (8096 free bytes per page):
Number of rows per page (Rows_Per_Page) = ( 8096 ) / (Row_Size + 2)

Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row.

If a clustered index is to be created on the table, calculate the number of reserved free rows per page, based on the fill factor specified. For more information, see Fill Factor. If no clustered index is to be created, specify Fill_Factor as 100.
Number of free rows per page (Free_Rows_Per_Page) = 8096 x ((100 - Fill_Factor) / 100) / (Row_Size + 2)

The fill factor used in the calculation is an integer value rather than a percentage.

Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. As the fill factor grows, more data will be stored on each page and there will be fewer pages.

Calculate the number of pages required to store all the rows:
Number of pages (Num_Pages) = Num_Rows / (Rows_Per_Page - Free_Rows_Per_Page)

The number of pages estimated should be rounded up to the nearest whole page.

Calculate the amount of space required to store the data in a table (8192 total bytes per page):
Table size (bytes) = 8192 x Num_Pages更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / 在SQL SERVER 上用何命令得到某个数据库表的大小?
    HOW TO get the size of a table in a Sql server.
    • What I found for SQL Server online help....
      本文发表在 rolia.net 枫下论坛Estimating the Size of a Table
      The following steps can be used to estimate the amount of space required to store the data in a table:

      Specify the number of rows present in the table:
      Number of rows in the table = Num_Rows

      If there are fixed-length and variable-length columns in the table definition, calculate the space that each of these groups of columns occupies within the data row. The size of a column depends on the data type and length specification. For more information, see Data Types.
      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

      If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:
      Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )

      Only the integer portion of the above expression should be used; discard any remainder.

      If there are variable-length columns in the table, determine how much space is used to store the columns within the row:
      Total size of variable-length columns (Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) + Max_Var_Size

      If there are no variable-length columns, set Variable_Data_Size to 0.

      This formula assumes that all variable-length columns are 100 percent full. If you anticipate that a lower percentage of the variable-length column storage space will be used, you can adjust the result by that percentage to yield a more accurate estimate of the overall table size.

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

      The final value of 4 represents the data row header.

      Calculate the number of rows per page (8096 free bytes per page):
      Number of rows per page (Rows_Per_Page) = ( 8096 ) / (Row_Size + 2)

      Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row.

      If a clustered index is to be created on the table, calculate the number of reserved free rows per page, based on the fill factor specified. For more information, see Fill Factor. If no clustered index is to be created, specify Fill_Factor as 100.
      Number of free rows per page (Free_Rows_Per_Page) = 8096 x ((100 - Fill_Factor) / 100) / (Row_Size + 2)

      The fill factor used in the calculation is an integer value rather than a percentage.

      Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. As the fill factor grows, more data will be stored on each page and there will be fewer pages.

      Calculate the number of pages required to store all the rows:
      Number of pages (Num_Pages) = Num_Rows / (Rows_Per_Page - Free_Rows_Per_Page)

      The number of pages estimated should be rounded up to the nearest whole page.

      Calculate the amount of space required to store the data in a table (8192 total bytes per page):
      Table size (bytes) = 8192 x Num_Pages更多精彩文章及讨论,请光临枫下论坛 rolia.net
      • And you can use 'sp_help tablename' to get all the info. to calculate the size.
    • sp_spaceused tablename
      • 我其实有些困惑,他是要table中所有fields的size,还是整个表所占用的空间?
        • I think it is table size according to his words.
          • thank u
    • there is an easy way to check it.
      1. If in sql 6.5, select the table , right click mouse and select "indexes"
      2. If in sql 7.0, select the database, from the menu->view, select "Taskpad", then u will find the information for all the talbes(including the size of the table) in this database