if object_id('dbo.sp_EstTableSizeData', 'P') IS NOT NULL drop procedure dbo.sp_EstTableSizeData
go
create procedure dbo.sp_EstTableSizeData
@ObjId int, @Num_Rows int,
@Has_ClusteredIndex int, @Has_UniqueClusteredIndex int,
@DataPages int OUTPUT
as
/*
**
** Do not call the procedure directly. It will be called
** from the main procedure: sp_estTableSize
**
**
*/
--<-- Addition #1: Computed columns do not consume physical space
--<-- Addition #2: A non-unique clustered index on the table adds a "uniqueifer" to
-- "subsequent" instances of duplicate keys. This "uniqueifer" appears
-- not only on the index pages, but also on the leaf (data) pages.
-- See sp_EstTableSizeCI for additional information.
set nocount on
declare @Num_Cols as smallint, @Num_Variable_Cols as smallint
declare @Fixed_Data_Size as int, @Max_Var_Size as int
declare @Null_Bitmap as smallint, @Variable_Data_Size as int
declare @Row_Size as int, @Rows_Per_Page as int
declare @Free_Rows_Per_Page as int
-- Pull together information about the columns, the size of the columns and whether the
-- column is fixed or variable
select @Num_Cols = count(*),
@Fixed_Data_Size = sum(sc.length * (1 - st.variable)),
@Num_Variable_Cols = sum(cast(st.variable as smallint)),
@Max_Var_Size = sum(sc.length * st.variable)
from sysobjects as so
join syscolumns as sc
on so.id = sc.id
join systypes as st
on sc.xtype = st.xtype
where so.id = @ObjId
and ObjectProperty (so.id, 'IsUserTable') = 1
and ColumnProperty (so.id, sc.name, 'IsComputed') = 0 --<-- Addition #1
set @Null_Bitmap = case when @Fixed_Data_Size = 0 then 0 else (2 + (( @Num_Cols + 7) / 8 ) ) end
set @Variable_Data_Size = case
when @Num_Variable_Cols = 0
then 0
else 2 + (@Num_Variable_Cols + @Num_Variable_Cols) +
@Max_Var_Size
end
set @Row_Size = @Fixed_Data_Size + @Variable_Data_Size + @Null_Bitmap + 4 +
case when @Has_ClusteredIndex = 1 and @Has_UniqueClusteredIndex = 0
then 4
else 0
end
set @Rows_Per_Page = 8096 / (@Row_Size + 2)
-- If there is a clustered index on the table, get the Fill Factor used
declare @Fill_Factor as int
select @Fill_Factor = case
when IndexProperty (@ObjId, name, 'IndexFillFactor') IS NULL
then 100
when IndexProperty (@ObjId, name, 'IndexFillFactor') = 0
then 100
else IndexProperty (@ObjId, name, 'IndexFillFactor')
end
from sysindexes
where id = @ObjId
and IndexProperty(@ObjId, name, 'IsClustered') = 1
set @Fill_Factor = Coalesce (@Fill_Factor, 100)
set @Free_Rows_Per_Page = 8096 * ((100 - @Fill_Factor) / 100.0) / @Row_Size
set @DataPages = ceiling (1.0 * @Num_Rows / (@Rows_Per_Page - @Free_Rows_Per_Page) )
RETURN(0)
