Calculating row size

[katbear]

Hi,

Is there a formula or quick query I can run to determine the average row size of a given table??

If not, what is the "standard" way of doing this?

Thanks

 

[ptheriault]

Kat,
From BOL (books on-line) Search for "Estimating the Size of a Database"


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

There isn't any true query to figure this out.

Numeric's are stored based on there sizes; bit is 1 byte, tinyint is 1 byte, smallint is 2 bytes, int is 4 bytes, bigint is 8 bytes, datetime is 8 bytes, smalldatetime is 4 bytes, etc (These sizes aren't exact but they are close enough). For CHAR take the size of the column. For NCHAR take the size of the column times 2. For VARCHAR use a query like this.

SELECT AVG(LEN(YourColumn))
FROM Table WITH (NOLOCK)

For NVARCHAR use the same query, but multiply by 2. For TEXT, NTEXT, and IMAGE you will have to guess.

Add these numbers up and you've got your average row size.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com