Estimating the table size...

,

Well.... it's bit lengthy; I have used 6 functions to get the table size. Table size returned is in KB.

 

  1. fn_CalculateHeapSize: This function calculates the size of heap i.e when no clustered index has been defined.
  2. fn_ClusteredIndexSize: This function calculate the clustered index size.
  3. fn_GetNonClusteredIndexSize: This function calculate the size of all non-clustered index.
  4. fn_GetTableSize: This function combines the result of above functions to give the final results.
  5. fn_getIndexSpace: A common function used for calculation in above functions.
  6. fn_GetLeafLevelIndexSpace: A common function used for calculation in above functions.
  • Usage: select dbo.fn_GetTableSize('tablename')

Note:

I have not considered the LOB cols. in my calculations; also this is an estimation of a table size as given in BOL "Estimating the table size"

 

CREATE function [dbo].[fn_CalculateHeapSize]
(@Tablename varchar(100))
Returns varchar(100)
AS
Begin
    -- Calculate the space used taken at leaf level 
    Declare @Num_Rows float,@Num_Cols int,@Fixed_data_size int,@Num_var_Cols int,@Max_var_size int
    Declare @Null_Bitmap int,@Variable_Data_Size int,@Heap_size bigint
    Declare @Row_Size int,@Rows_per_page float,@Num_Pages float
    
    set @Num_Rows=(select [rows] from sys.sysindexes where indid=1 and id=object_id(@Tablename))
    
    set @Num_Cols=(select count(*) from sys.columns where object_id=Object_id(@Tablename))
    
    set @Fixed_data_size=(select sum(max_lenGth) from sys.columns where object_id=Object_id(@Tablename) 
        and system_type_id not in (165,167,231,34,35,99))
    
    set @Num_var_Cols=(select count(*) from sys.columns where object_id=Object_id(@Tablename) 
    and system_type_id in (165,167,231,34,35,99))
    
    set @Max_var_size=(select sum(max_lenGth) from sys.columns where object_id=Object_id(@Tablename) 
    and system_type_id in (165,167,231,34,35,99))
    
    set @Null_Bitmap= 2 + (@Num_Cols + 7)/8
    
    If( @Num_var_Cols = 0)
    BEGIN
        set @Variable_Data_Size = 0
    END
    ELSE
    begin
    set @Variable_Data_Size = 2 + (@Num_var_Cols * 2) + @Max_var_size
    END
    
    set @Row_Size = @Fixed_data_size + @Variable_Data_Size + @Null_Bitmap + 4 -- Row header info
    
    set @Rows_per_page= 8096/(@Row_Size + 2)
    
    -- No. of pages needed to store rows
    set @Num_Pages= ceiling(@Num_Rows/@Rows_per_page)
    
    set @Heap_size = (8192 * @Num_Pages)/1024
    
    -- Space used to store index info
    return Ltrim(str(@Heap_size))-- + ' KB'
    End

CREATE function [dbo].[fn_ClusteredIndexSize]
(@Tablename varchar(100))
Returns BigInt
AS
Begin
    -- Calculate the space used taken at leaf level 
    Declare @Num_Rows float,@Num_Cols int,@Fixed_data_size int,@Num_var_Cols int,@Max_var_size int,@fill_factor int
    Declare @uniquifier smallint,@uniquefiersize smallint,@Null_Bitmap int,@Variable_Data_Size int,@Total_Space varchar(100)
    Declare @Row_Size int,@Rows_per_page float,@Free_rows_per_page float,@level float ,@Num_Pages float,@Leaf_level_space int
    set @uniquifier=1
    set @uniquefiersize=4
    set @Num_Rows=(select [rows] from sys.sysindexes where indid=1 and id=object_id(@Tablename))
    
    set @Num_Cols=(select count(*) from sys.columns where object_id=Object_id(@Tablename))
    
    set @Fixed_data_size=(select sum(max_lenGth) from sys.columns where object_id=Object_id(@Tablename) 
        and system_type_id not in (165,167,231,34,35,99))
    
    set @Num_var_Cols=(select count(*) from sys.columns where object_id=Object_id(@Tablename) 
    and system_type_id in (165,167,231,34,35,99))
    
    set @Max_var_size=(select sum(max_lenGth) from sys.columns where object_id=Object_id(@Tablename) 
    and system_type_id in (165,167,231,34,35,99))
    
    If ( (select is_unique from sys.indexes where type=1 and object_id=Object_id(@Tablename)) = 0 ) 
        Begin
            set @Num_Cols = @Num_Cols + @uniquifier
            set @Num_var_Cols = @Num_var_Cols + @uniquifier
            set @Max_var_size = @Max_var_size + @uniquefiersize
        End 
    set @Null_Bitmap= 2 + (@Num_Cols + 7)/8
    
    set @Variable_Data_Size = 2 + (@Num_var_Cols * 2) + @Max_var_size
    
    set @Row_Size = @Fixed_data_size + @Variable_Data_Size + @Null_Bitmap + 4 -- Row header info
    
    set @Rows_per_page= 8096/(@Row_Size + 2)
    
    set @fill_factor=(select fill_factor from sys.indexes where object_id=object_id(@Tablename) and type =1)
    
    -- No. of reserved free rows per page
    set @Free_rows_per_page = 8096 * (((100 - @Fill_Factor) / 100) / (@Row_Size + 2))
    
    -- No. of pages needed to store rows
    set @Num_Pages= ceiling((@Num_Rows/(@Rows_per_page - @Free_rows_per_page)))
    
    set @Leaf_level_space = 8192 * @Num_Pages
    
    -- Space used to store index info
    Declare @Num_Key_cols int,@Fixed_key_size int,@Num_var_key_cols int,@Max_var_key_size int
    Declare @Index_Null_Bitmap int,@Variable_Key_size int,@Index_row_size int,@Index_row_per_page float,@levels int
    Declare @Num_Index_pages int,@Index_level_space int,@Null_Cols int
    Set @Num_Key_cols=(select Keycnt from sys.sysindexes where id=object_id(@Tablename) and indid=1)
    
    Set @Fixed_key_size = (select sum(max_length) from sys.index_columns a,sys.indexes b,sys.columns c where a.index_id=b.index_id 
        and b.object_id=object_id(@Tablename) and type=1 and a.object_id=b.object_id
        and c.object_id=b.object_id and a.column_id=c.column_id and system_type_id not in (165,167,231,34,35,99))
    
    set @Num_var_key_cols = (select count(c.name) from sys.index_columns a,sys.indexes b,sys.columns c where a.index_id=b.index_id 
    and b.object_id=object_id(@Tablename) and type=1 and a.object_id=b.object_id
    and c.object_id=b.object_id and a.column_id=c.column_id and 
    system_type_id in (165,167,231,34,35,99))
    
    set @Max_var_key_size = (select IsNull(sum(max_length),0) from sys.index_columns a,sys.indexes b,sys.columns c where a.index_id=b.index_id 
    and b.object_id=object_id(@Tablename) and type=1 and a.object_id=b.object_id
    and c.object_id=b.object_id and a.column_id=c.column_id and 
    system_type_id in (165,167,231,34,35,99))
    
    If ( (select is_unique from sys.indexes where type=1 and object_id=Object_id(@Tablename)) = 0 ) 
        Begin
            set @Num_Key_cols = @Num_Key_cols + @uniquifier
            set @Num_var_key_cols = @Num_var_key_cols + @uniquifier
            set @Max_var_key_size = @Max_var_key_size + @uniquefiersize
        End 
    
    set @Null_Cols=(select IsNull(count(c.name),0) from sys.index_columns a,sys.indexes b,sys.columns c where a.index_id=b.index_id    
    and b.object_id=object_id(@Tablename) and type=1 and a.object_id=b.object_id
    and c.object_id=b.object_id and a.column_id=c.column_id and c.is_nullable=1)
    
    select @Index_level_space=dbo.fn_getIndexSpace(@Null_Cols,
                                    @Num_var_key_cols,
                                    @Max_var_key_size,
                                    @Fixed_key_size,
                                    @Num_Rows)
    set @Total_space = Ltrim(str((@Index_level_space+@Leaf_level_space)/(1024)))
    return @Total_space
End
CREATE Function [dbo].[fn_getIndexSpace]
( @Num_Null_key_cols int,@Num_var_key_cols int,@Max_var_key_size int,@Fixed_key_size int,@Num_Rows float )
returns Bigint 
AS
    BEGIN        
    Declare @Index_Null_Bitmap int,@Var_Key_Size int,@Index_row_Size int,@Index_Row_per_Page float
    Declare @level float,@Num_Index_pages int,@Index_Space_Used bigint
        If (@Num_Null_key_cols>0)
            Begin
            set @Index_Null_Bitmap = 2 + ((@Num_Null_key_cols+7)/8)
            End
            Else
            Begin
            set @Index_Null_Bitmap=0
            End
            
            IF (@Num_var_key_cols>0)
            BEGIN
            set @Var_Key_Size = 2 + (@Num_var_key_cols*2) + @Max_var_key_size
            END
            ELSE
            begin
                set @Var_Key_Size=0
            END    
    
            set @Index_row_Size=@Fixed_key_size + @Var_Key_Size + @Index_Null_Bitmap + 1+6
            set @Index_Row_per_Page = 8096/(@Index_row_Size +2)
            set @level = 1 + floor(abs((log10(@Num_Rows/@Index_row_per_page)/log10(@Index_row_per_page))))
            set @Num_Index_pages=0
                Declare @i int
                    if (@level>0)
                    Begin
                        set @i=1
                        while(@i<=@Level)
                        Begin
                        set @Num_Index_pages = @Num_Index_pages + power(@Index_row_per_page,@level - @i)
                        set @i= @i + 1
                        End    
                    END
        
        set @Index_Space_Used = (8192 * @Num_Index_pages)
        Return @Index_Space_Used

End

CREATE Function [dbo].[fn_GetLeafLevelIndexSpace]
(@Num_Leaf_Cols int,@Num_Var_leaf_Cols int,@Max_var_leaf_size int,@Fixed_Leaf_Size int,@Fill_Factor int,@Num_Rows float)
Returns bigint
AS 
BEGIN

    Declare @Leaf_Null_Bitmap int,@Variable_leaf_size int,@Leaf_Row_Size int,@Leaf_Rows_per_page int
    Declare @Free_Rows_Per_Page int,@Num_Leaf_Pages float, @Leaf_Space_Used int
        
    set @Leaf_Null_Bitmap= 2 + ((@Num_Leaf_Cols + 7)/8)
    
    If (@Num_Var_leaf_Cols>0)
        Begin
            set @Variable_leaf_size = 2 + (@Num_Var_leaf_Cols * 2) + @Max_var_leaf_size
        END
        ELSE
        Begin 
            set @Variable_leaf_size = 0
        END


    set @Leaf_Row_Size = @Fixed_Leaf_Size + @Variable_leaf_size + @Leaf_Null_Bitmap + 1+ 6

    set @Leaf_Rows_per_page = 8096 / (@Leaf_Row_Size + 2)

    set @Free_Rows_Per_Page= 8096 * (((100 - @Fill_Factor) / 100) / (@Leaf_Row_Size + 2))
    
    set @Num_Leaf_Pages = ceiling((@Num_Rows/(@Leaf_Rows_per_page - @Free_Rows_Per_Page)))
    
    set @Leaf_Space_Used = 8192 * @Num_Leaf_Pages

    return @Leaf_Space_Used
END
    


CREATE function [dbo].[fn_GetNonClusteredIndexSize] 
(@TableName varchar(100))
Returns bigint
AS
Begin

Declare @Num_Rows float,@Num_Key_cols int,@Fixed_key_size int,@Num_var_key_cols int,@Max_var_key_size int
Declare    @is_clustered int,@index_id int,@is_unique bit,@Num_Diff_cols int,@Num_Null_key_cols int
Declare @Num_Index_pages int,@Index_Space_Used int,@Total_Index_space bigint
Declare @Num_Leaf_Cols int,@Num_Included_Cols int,@Leaf_Level_Space int,@Fill_Factor int
-- CALCULATE THE SPACE USED TO SAVE INDEX INFORMATION AT NON-LEAF LEVEL
-- No of Rows in a table
    set @Total_Index_space=0
    set @Leaf_Level_Space=0    
    -- insert info intom temp table
    set @Num_Rows=(select [rows] from sys.sysindexes where indid=1 and id=object_id(@TableName))
    Declare @Tmp_Info Table 
    ( Index_id int,Num_key_cols int,type int,is_unique bit,is_included smallint,fill_factor int,Num_Var_Key_cols int,
     Fixed_Key_Size int,Max_Var_Key_Size int
    )
    Declare @tmp_Index_info Table
    (sno int identity(1,1),index_id int,Num_key_cols int,type int,is_unique bit,Num_Var_Key_cols int,
     Fixed_Key_Size int,Max_Var_Key_Size int,Num_Included_Col int,fill_factor int
    )
    insert into @Tmp_Info
    
    select b.Index_id,count(c.name) Num_key_cols,b.type,b.is_unique,is_included_column,fill_factor, 
    IsNull((select count(c.name) from sys.columns e where e.object_id=c.object_id and a.column_id=e.column_id
    and c.system_type_id in (165,167,231,34,35,99)),0) As Num_Var_Key_cols,
    ISNULL((select sum(max_length) from sys.indexes d where d.index_id=b.index_id and d.object_id=c.object_id 
        and c.system_type_id not in (165,167,231,34,35,99)),0) As Fixed_Key_Size,
    ISNULL((select sum(max_length) from sys.indexes d where d.index_id=b.index_id and d.object_id=c.object_id 
        and c.system_type_id in (165,167,231,34,35,99)),0) As Max_Var_Key_Size
    --into @Tmp_Info
     from sys.index_columns a,sys.columns c,sys.indexes b 
    where a.column_id=c.column_id and a.index_id=b.index_id and b.object_id=c.object_id and
    a.object_id=b.object_id    and b.object_id=object_id(@TableName) --and b.type>1
    group by c.name,b.index_id,c.object_id,c.system_type_id,a.column_id,b.type,
    b.is_unique,is_included_column,fill_factor
    order by b.index_id

    
    insert into @tmp_Index_info
    select index_id As Index_id,sum(num_key_cols) as num_key_cols,type,is_unique,sum(Num_var_key_cols) as Num_var_key_cols,
    sum(Fixed_key_size) as Fixed_key_size,sum(max_var_key_size) as max_var_key_size,sum(is_included),fill_factor
    --into @tmp_Index_info
    from @Tmp_Info where type>1 group by index_id,type,is_unique,fill_factor
        
    IF Exists(select 1 from @Tmp_Info where type=1)
    Begin 
        Set @is_clustered = 1
        
    END
    ELSE
    BEGIN
        Set @is_clustered = 0
        
    END

    Declare @row_Count int
    set @row_Count=(select count(*) from @tmp_Index_info where type>1)
    while (@row_Count>0)
    begin
            
        select @index_id=index_id,@Num_Key_cols=num_key_cols,@Fixed_key_size=fixed_key_size,@Num_var_key_cols=Num_var_key_cols,
        @Max_var_key_size=Max_var_key_size,@is_unique=is_unique,
        @Num_Included_Cols=Num_Included_Col,@Fill_Factor=fill_factor
        from @tmp_Index_info where sno=@row_Count

        If (@is_clustered=0)
            Begin    
                set @Num_Key_cols = @Num_Key_cols + 1
                set @Num_Leaf_Cols= @Num_Key_cols + @Num_Included_Cols + 1
            END
            ELSE
                BEGIN
                select @Num_Diff_cols=count(column_id) from sys.index_columns x,sys.indexes y
                where column_id not in 
                (
                 select column_id from sys.index_columns a,sys.indexes b 
                 where a.index_id=b.index_id and type>1 and a.object_id=b.object_id and
             a.object_id=object_id(@TableName) and a.index_id=@index_id
                )
                and x.object_id=y.object_id and y.type=1 and x.object_id=object_id(@TableName)
                and x.index_id=y.index_id
        
                set @Num_Key_cols = @Num_Key_cols + @Num_Diff_cols + @is_unique

                set @Num_Leaf_Cols = @Num_Key_cols + @Num_Included_Cols + @Num_Diff_cols + @is_unique
                    
            END

            select @Num_Null_key_cols=ISNULL(count(x.column_id),0) from sys.index_columns x,sys.columns y 
            where x.column_id=y.column_id and x.index_id=@index_id and y.is_nullable=1
            and x.object_id=object_id(@TableName) and x.object_id=y.object_id
        declare @index_name varchar(100)
        
        select @index_name=name from sys.indexes where object_id=object_id(@TableName) and index_id=@index_id
        select @Index_Space_Used=dbo.fn_getIndexSpace(@Num_Null_key_cols,
                                    @Num_var_key_cols,
                                    @Max_var_key_size,
                                    @Fixed_key_size,
                                    @Num_Rows)
        select @Leaf_Level_Space=dbo.fn_GetLeafLevelIndexSpace(@Num_Leaf_Cols,@Num_var_key_cols,
                    @Max_var_key_size,@Fixed_key_size,@Fill_Factor,@Num_Rows)
        
        set @Total_Index_space= @Total_Index_space + @Index_Space_Used + @Leaf_Level_Space
        
        set @row_Count=@row_count-1    
END            
            
            return Ltrim(str((@Total_Index_space))/(1024))
END

Create Function [dbo].[fn_GetTableSize]
(@TableName varchar(100))
returns varchar(25)
AS
Begin
Declare @TableSize varchar(25)
If Exists(select 1 from sys.indexes where object_id=object_id(@TableName) and type=1)
    Begin
        select @TableSize = dbo.fn_ClusteredIndexSize(@TableName) + dbo.fn_GetNonClusteredIndexSize(@TableName)
    END
    ELSE
    BEGIN     
        select @TableSize = (dbo.fn_CalculateHeapSize(@TableName) + dbo.fn_GetNonClusteredIndexSize(@TableName))
    END
    set @TableSize = Ltrim(str(@TableSize)) + ' KB'
return @TableSize
END

Rate

1 (1)

Share

Share

Rate

1 (1)