Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm building now a new sql 2005 database for my company, and we expect this database to be huge with 100's of millions of records. We already know we need to dedicate a separate server with new hard drives for this task. I need to know how big of hard drives to get. I don't want to just go now and buy big and simple hard drives. I think it's better to get the small but very fast ram drives or other solutions that cost a lot. I would like to know in a specific sql table what will be the size of each record then i'll multiple it with the amount of records i expect, so i'll know what will be the size of my database in a year/2 years... Is there a tool that will be able to estimate it?

Thanks.

share|improve this question

migrated from stackoverflow.com Jan 25 '12 at 13:42

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 4 down vote accepted

If you already have some representative sample data, just run the following to find the average row size:

sp_spaceused 'Tablename'

Otherwise you can estimate its size without indexes with this function:

CREATE FUNCTION available_tablerowsize 
(
-- Add the parameters for the function here
@tablename char(50)
)
RETURNS int
AS
BEGIN
-- variables to track fixed and variable column sizes   
DECLARE @num_columns int
DECLARE @result int
DECLARE @num_fixed_columns int
DECLARE @fixed_data_size int
DECLARE @var_data_size int
DECLARE @num_var_columns int
DECLARE @max_var_size int
DECLARE @null_bitmap_size int
DECLARE @row_size int

-- Find the total number of columns
select @num_columns = count(*)
from syscolumns,systypes 
where syscolumns.id=object_id(@tablename) 
and syscolumns.xtype=systypes.xtype 


-- Find the size occupied by fixed length columns (Note: not possible to exist outside the 8060 bytes limit)
select @num_fixed_columns = count(*)
from syscolumns,systypes 
where syscolumns.id=object_id(@tablename) 
and syscolumns.xtype=systypes.xtype and systypes.variable=0

select @fixed_data_size = sum(syscolumns.length) 
from syscolumns,systypes 
where syscolumns.id=object_id(@tablename) 
and syscolumns.xtype=systypes.xtype and systypes.variable=0

-- Find the size occupied by variable length columns within the 8060 page size limit 

-- number of variable length columns
select @num_var_columns=count(*)
from syscolumns, systypes
where syscolumns.id=object_id(@tablename) 
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- max size of all variable length columns
select @max_var_size =max(syscolumns.length) 
from syscolumns,systypes 
where syscolumns.id=object_id(@tablename) 
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- calculate variable length storage
begin
if @num_var_columns>0
set @var_data_size=2+(@num_var_columns*2)+@max_var_size
--set @var_data_size = @num_var_columns*24
else
set @var_data_size=0
end

-- If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability.
select @null_bitmap_size = 2 + ((@num_columns+7)/8)

-- Calculate total rowsize
select @row_size = @fixed_data_size + @var_data_size + @null_bitmap_size + 4

-- Return the available bytes in the row available for expansion
select @result = 8060 - @row_size 

RETURN @result


END
GO
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.