June 16, 2003 at 3:17 pm
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
June 17, 2003 at 8:45 am
Is your table size about 5 MB
MW
MW
June 17, 2003 at 10:36 am
Why don't you just run this
sp_MStablespace tablename
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply