average row length per table, number of rows and Row/Block/object over 2

[myvision69]

is there a way to determine the average row length per table, number of rows and Row/Block/object overhead?


get information about the object size =(Average row length per table * number of rows) + (Row/Block/Object overhead)


Thanks

 

[ptheriault]

Here are two commands that will offer you information about the size of a table.

DBCC SHOWCONTIG(table_name)

sp_spaceused table_name

- Paul
- Database performance looks fine, it must be the Network!

 

[mrdenny]

Finding the average row length per table is fairly easy.

Add up the length of the number, datetime, uniqueidentifier and char fields. Then use the AVG and LEN functions to get the average lengths of the VARCHAR and NVARCHAR fields. (If the data type is NCHAR, NVARCHAR or NTEXT you have to double the number of characters to get the number of bytes.) For any variable length fields add 2 bytes per field.

For TEXT, NTEXT and IMAGE fields you'll need to guestimate the data size.

Add up all the lengths and you've got the average row size for the table.

Divide the average row size into 8060 to find out how many records will fit in each page (not accounting for padding). If you get a decimal, round down as you can't put a partial record in a page (SQL doesn't support page spliting).

To get the number of 8k pages divide the number of records by the number of records per page. This will tell you how many 8k pages you need.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com