MSDN's Estimating the Size of a Table

[TZone]

I was hoping I can get some help regarding subject mater located at
http://msdn.microsoft.com/library/de...es_02_92k3.asp

MyTable has 23 fields with 100,000 records.
Field1 nvarchar 90
Field2 char 6
Field3 varchar 8000
Field4 nvarchar 200
Field5 nvarchar 200
Field6 nvarchar 200
Field7 nvarchar 200
Field8 nvarchar 200
Field9 char 30
Field10 char 30
Field11 nvarchar 200
Field12 nvarchar 200
Field13 float 8
Field14 datetime 8
Field15 datetime 8
Field16 datetime 8
Field17 nvarchar 200
Field18 nvarchar 200
Field19 varchar 8000
Field20 nvarchar 200
Field21 nvarchar 200
Field22 nvarchar 200
Field23 varchar 8000

Data Types:
nvarchar = Variable-length, Storage is 2 times the # of characters entered
char = Fixed-length. Storage is n bytes
varchar = Variable-length, Storage is actual length in bytes of data entered
float = Is float Fixed-length?, my precisions are 15 digits so 8 bytes.
datetime = Is datetime Fixed-length?, Storage is 8 bytes
I need the following blanks filled in for me, please :-)

Num_Rows 100,000
Num_Cols 23
Fixed_Data_Size
Num_Variable_Cols 16
Max_Var_Size
Null_Bitmap Int(2 + ((23 + 7) / 8)) = 5?
Variable_Data_Size
Row_Size
Rows_Per_Page
Free_Rows_Per_Page 100 (no clustered index created)
Num_Pages
Table Size

[Simon Hayes]

"TZone" <TZ***@optus.com.au> wrote in message
news:3f***********************@news.optusnet.com.a u...

I was hoping I can get some help regarding subject mater located at
http://msdn.microsoft.com/library/de...es_02_92k3.asp

<snip>

You're correct that float and datetime are fixed length, and both are indeed
8 bytes (for your float precision, as you noted), which seems to be your
main question. You just need to follow the formulas in the documentation.

One point you may need to consider is how full the average variable length
column will be - if you know that almost all data in an nvarchar(100) column
will be around 50 Unicode characters, ie. half the length of the field, then
you could count that as 100 bytes instead of 200 to get a more accurate
estimate. Or if the average will be around 75, then count 150 etc. But
you're the only person who knows what your data looks like, so you're the
best person to calculate the size of the table.

Simon

[TZone]

After following formula in documentation I arrive at:

Num_Rows = 100,000
Num_Cols = 23
Fixed_Data_Size = 98 bytes
Num_Variable_Cols = 16 fields
Max_Var_Size = 8000 bytes ** Field3 is varchar with length 8000, so is this
8000 bytes?***
Null_Bitmap = 5
Variable_Data_Size = 8034
Row_Size = 8141
Rows_Per_Page = (INT(8096/(Row_Size + 2)) ** is zero correct?**
Free_Rows_Per_Page = 100 (I have no clustered index)
Num_Pages = -1000 ** is this correct, a negative Num_Pages?**
Total Size (Mb) = -7.81

MyTable has 23 fields with 100,000 records.
Field1 nvarchar 90 * variable*
Field2 char 6 *fixed*
Field3 varchar 8000 *variable*
Field4 nvarchar 200 *variable*
Field5 nvarchar 200 *variable*
Field6 nvarchar 200 *variable*
Field7 nvarchar 200 *variable*
Field8 nvarchar 200 *variable*
Field9 char 30 *fixed*
Field10 char 30 *fixed*
Field11 nvarchar 200 *variable*
Field12 nvarchar 200 *variable*
Field13 float 8 *fixed*
Field14 datetime 8 *fixed*
Field15 datetime 8 *fixed*
Field16 datetime 8 *fixed*
Field17 nvarchar 200 *variable*
Field18 nvarchar 200 *variable*
Field19 varchar 8000 *variable*
Field20 nvarchar 200 *variable*
Field21 nvarchar 200 *variable*
Field22 nvarchar 200 *variable*
Field23 varchar 8000 *variable*

I'm going to assume all variable fields are close to maximum capacity.

Thanks for previous reply Simon!


"Simon Hayes" <sq*@hayes.ch> wrote in message
news:3f**********@news.bluewin.ch...

"TZone" <TZ***@optus.com.au> wrote in message
news:3f***********************@news.optusnet.com.a u...

I was hoping I can get some help regarding subject mater located at

http://msdn.microsoft.com/library/de...es_02_92k3.asp

<snip>

You're correct that float and datetime are fixed length, and both are

indeed 8 bytes (for your float precision, as you noted), which seems to be your
main question. You just need to follow the formulas in the documentation.

One point you may need to consider is how full the average variable length
column will be - if you know that almost all data in an nvarchar(100) column will be around 50 Unicode characters, ie. half the length of the field, then you could count that as 100 bytes instead of 200 to get a more accurate
estimate. Or if the average will be around 75, then count 150 etc. But
you're the only person who knows what your data looks like, so you're the
best person to calculate the size of the table.

Simon