Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3625
  • 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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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