Table Size

• cdun2

  SSCertifiable

  Points: 7893

  More actions

  June 16, 2003 at 3:17 pm

  #160381

  Hello,

  I am trying to calculate the size of a table using the steps in BOL, and I want to make sure that I am doing this correctly. The table contains one Primary Key Constraint on one field, and was created with the default Fill Factor of the Server (zero). I am not sure as to how I should work the BIT fields into the equation, and I ran into a problem when trying to figure in the Clustered index. In the table I have the following data types, sizes and frequency of use:

  VARCHAR (100), 1 field

  VARCHAR (12), 1 field

  VARCHAR (2), 201 fields

  BIT, 57 fields

  Twelve of the BIT fields have a default of zero (and have to be either zero or one), the other 45 BIT fields can be zero, one, or NULL. That being the case for the BIT fields, I think I can break down the data about the table as follows:

  NUM_ROWS: 9,237 (this is the maximum number that the table can have)

  NUM_COLS: 260

  FIXED_DATA_SIZE: 45 (The BIT fields that can be NULL)

  NUM_VARIABLE_COLS: 215 (Including the 12 BIT fields that must have a value)

  MAX_VAR_SIZE: 100 (The largest VARCHAR field I have is 100)

  The following is my first attempt at the calulation:

  NULL_BITMAP=35 2+((260+7)/8)

  VARIABLE_DATA_SIZE(100% full)=532 2+(215*2)+100

  ROW_SIZE=616 45+532+35+4

  ROWS_PER_PAGE=13 (8096)/(616+2)

  In the next step, I run into a problem trying to figure in the Clustered Index:

  #_OF_FREE_ROWS_PER_PAGE=48 8096*((100-0)/100)/(616+2)

  #_OF_PAGES=9272 9237/(13-48)

  The size of the table does not seem correct:

  TABLE_SIZE=75956224 BYTES 8192*9272

  What steps do I need to take to correct this calculation so that I get the correct table size?

  Thanks!

  CSDunn

• mworku

  Ten Centuries

  Points: 1311

  More actions

  June 17, 2003 at 8:45 am

  #461761

  Is your table size about 5 MB

  MW

  
  MW

• Kay_dba

  Mr or Mrs. 500

  Points: 528

  More actions

  June 17, 2003 at 10:36 am

  #461782

  Why don't you just run this

  sp_MStablespace tablename