Sql. Calculating the size in KB of each row

Question

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.

Answer 1

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