CODEWITHSUNDEEP
sql-server-2008diskspace
Jim B
Jim B

Reputation: 8574

How much disk space do SQL Columns use

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!

Upvotes: 9

Views: 9687

Answers (3)

John
John

Reputation: 7049

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

Upvotes: 7

JNK
JNK

Reputation: 65147

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

Upvotes: 2

Dewfy
Dewfy

Reputation: 23614

There is big difference between column and real record allocation.

For types:

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.

Upvotes: 4

Related Questions