Link to home
Start Free TrialLog in
Avatar of Rainbow002
Rainbow002

asked on

Find SQL average row size

Hi Experts,
SQL 2000.

How do we find out Average bytes  in each row. in   In other words i am looking for average row size in a table.  

Thanks
-R
ASKER CERTIFIED SOLUTION
Avatar of vasureddym
vasureddym

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vasureddym
vasureddym

Correction 1) angel is correct, this wont work for numeric data type columns

Correction 2) need to add ISNULL(len(col1), 0) for all the columns. If a column is NULL, the whole sum becomes null to ISNULL will save from such situation. Be careful with NULL in SQL Server.
just addition to Correction 1 above:
use datalength () function instead of len() finction. see the code
SELECT AVG(total_len) FROM (
        SELECT	ISNULL(DATALENGTH(col1), 0)+ 
		ISNULL(DATALENGTH(col2), 0) + 
		ISNULL(DATALENGTH(col3), 0)...... AS total_len
        FROM yourtable
) AS a

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial