SQL Server Determine Physical Size of Table Columns

Question

Hi,

Is it possible to find out how big the data is in KB or MB for individual columns in a table? I have a script which tells me the physical size of each table, but I would like to know how much of that is taken up by certain columns in the database, especially when I have XML stored in a column.

Any help much appreciated

Cheers

Answer 1

Not to my knowledge, sorry. Rarely wanted. For others to answer that - what version of SQL Server are you using - may be relevant here.

Answer 2

You should be able to use the datalength function, something like

select sum(datalength(yourfield))
from yourtable

This will summate the datalengths of all the entries of that field in the table - it will not account for overheads such as variable length field pointers, space in the nullability bitmap etc.