• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3635
  • Last Modified:

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
0
Rainbow002
Asked:
Rainbow002
  • 3
3 Solutions
 
vasureddymCommented:
one straight forward method is....
 

select AVG(total_len) from (
	select len(col1)+ len(col2) + len(col3)...... as total_len
	from yourtable
) as a

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that formula will give a quite good aprox, however it does not accomodate the fact that numerical data date data types are not stored in characters, but with the bit representation of the numerical value. len(1000000) returns 7, but it its a normal int data type, it will still only require 4 bytes ...
0
 
vasureddymCommented:
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.
0
 
vasureddymCommented:
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

0
 
Scott PletcherSenior DBACommented:
If you want the total row length, not just the data length, be sure to add:

1) An extra two bytes for each variable length column.

2) An extra byte for every 8 columns, rounded up (so 7 cols = 1 byte, 9 cols = 2 bytes).

3) An extra ~10 bytes per row of general SQL overhead (row ptrs, etc.).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now