How do we find the typical/average space occupied by column on disk?

Database
Enthusiast

How do we find the typical/average space occupied by column on disk?

I know we can get the max length of a column.
But when it comes to datatypes like varchar, etc where it is not necessary that it occupies all the bytes allotted to it, how do we calculate the average space occupied in that column in all the rows of a particular table.
3 REPLIES
Enthusiast

Re: How do we find the typical/average space occupied by column on disk?

May be you can try this function
select avg(character(trim(column_a))) from table_name;
Though the column_a here is a VARCHAR type, character(trim(column_a) gives the exact length it occupies.
Enthusiast

Re: How do we find the typical/average space occupied by column on disk?

Thanks a ton maddy. Your idea really helped.
rgs
Enthusiast

Re: How do we find the typical/average space occupied by column on disk?

Using TRIM will truncate trailing and leading spaces in the VARCHAR string. If you want the average length it takes up on disk you don't want to trim the spaces since they take up space also. If your character set is UNICODE then each character takes up 2 bytes. So if you want to get the space taken in bytes you have multiply it by 2. If your character set is the various Kanji versions then its best to read the manual on that. Sometimes it returns bytes at other times it returns a logical character length. Also the stored VARCHAR string has a length field that takes 2 bytes which is always there even if the string is zero length.