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

## 0 comments:

## Post a Comment