SQL Table Row Size

If you ever designed a table, you must have thought how much space this table will take on disk, memory, how can I estimate the amount of memory for the server etc'. well, I can't help you there, there are too many things to take into consideration to write in a short blog post.


However, I can help you with one thing, estimating the size of a row, just double it with the amount of rows you estimate will be in the table and you have a pretty close number, but you do have to know which indexes you'll have, they might even double the amount of space needed, this article will explain in detail how you can get a more accurate estimate.


Thanks to Ruchir T, who partially implemented this article, I just needed to make a few modifications and voila, you can now start estimating.

---- =============================================
---- Author: Ruchir T (http://www.west-wind.com/weblog/posts/2004/Jan/19/Sql-Server-Row-Size-Limit)
---- Create date: 01/02/2008
---- Update date: 2012-03-14 show all row info
---- Description: returns the number of bytes left to use for creating new columns
---- =============================================
CREATE FUNCTION usfTable_estimates
(    
    @tablename char(50)
)
RETURNS 
    @rettable table
    (
        TableName nvarchar(255),
       num_columns int,
       num_fixed_columns int,
       fixed_data_size int,
       num_var_columns int,
       max_var_size int,
       var_data_size int,
       null_bitmap_size int,
       row_size int,
       bytes_available int
    ) 
AS
begin 


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

insert into @rettable
select @tablename as TableName,
       @num_columns as num_columns,
       @num_fixed_columns as num_fixed_columns,
       @fixed_data_size as fixed_data_size,
       @num_var_columns as num_var_columns,
       @max_var_size as max_var_size,
       @var_data_size as var_data_size,
       @null_bitmap_size as null_bitmap_size,
       @row_size as row_size,
       @result as bytes_available
       
      return
    
end
GO

Tags: planning, sql