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!
Yes. Empty and NULL columns do take up space.
Configuration storage and IOPS We recommend that you allocate 2 GB for the Configuration database and 1 GB for the Central Administration content database. Over time, the configuration database may grow beyond 1 GB. It does not grow quickly — it grows by approximately 40 MB for each 50,000 site collections.
Blanks spaces are restricted in the naming convention of the database object's name and column name of the table. If you want to include the blanks space in the object name or column name, the query and application code must be written differently. You must be careful and precise while writing dynamic SQL queries.
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
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With