How much disk space do SQL Columns use

Question

Does anybody know of a link where I can find this stuff out? I'm working on a proposal to drop a whole bunch of unused columns from a few tables, and if i could find out the amount of disk space used, that would really help me out.

For example, if i have a table with 5.5 million rows, how much space will i save if i drop a BIT/INT32/DECIMAL (18,2) column?

This is SQL Server 2008.

Thanks again!

Answer 1

This piece of sql goes over all your columns and gives you the aggregate of their datalength().

I realize that this isn't exactly what the OP was asking - it's for the benefit of the poor souls who google "sql server column space used" and find this question.

It's also in my gist here.

create table #space ([table] nvarchar(255), [column] nvarchar(255) not null, [bytes] bigint null);

declare @sql varchar(max) = ''
declare @tablepattern as varchar(255) = '%'
declare @exclusionpattern as varchar(255) = ''

select @sql = @sql + 'insert into #space select ''' + t.name + ''', ''' + c.name + ''', sum(datalength([' + c.name + '])) as bytes from [' + t.name + '];' 
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name like @tablepattern and t.name not like @exclusionpattern;

exec (@sql)

select [table], format(sum([bytes]), '#,#') as [size]
from #space
group by [table]
order by sum(bytes) desc;

select [table], [column], format([bytes], '#,#') as [size]
from [#space]
order by [bytes] desc;

drop table #space

Answer 2

There is big difference between column and real record allocation.

For types:

• bit - is an integer that will be grouped to single physical column (saying 4byte integer) when you have more than one bit column in the record
• int32 - takes 4 bytes
• DECIMAL is very similar to structure described in http://msdn.microsoft.com/en-us/library/ms221061(v=VS.85).aspx (12-byte)

But in real world the columns are grouped to record with some alignment rules. Records are allocated by large pages, that can contains thousand records. The disk space is also affected by transaction journal - that partially saves some records. So it is difficult to deduce linear dependency of column size.

Answer 3

This is Per ROW

For numerics:

tinyint  1 byte
smallint 2 bytes
int      4 bytes
bigint   8 bytes

Bit is aggregated across the record so it's hard to say without knowing your structure. It's unlikely to save much.

DECIMAL will depend on the precision:

1 - 9   5 bytes
10 - 19 9 bytes
20 - 28 13 bytes
29 - 38 17 bytes