Home
/
SQL

SQL sp_EstTableSize

sp_EstTableSize

if object_id('dbo.sp_EstTableSize', 'P') IS NOT NULL  drop procedure dbo.sp_EstTableSize
go

create procedure dbo.sp_EstTableSize
	@TableName sysname = NULL,
	@Num_Rows int = NULL
as

-- Calculations and logic are based upon:
-- The Microsoft SQL Server 7.0 Books Online, plus
-- "Inside Microsoft SQL Server 7.0" by Ron Soukup and Kalen Delaney,
--   published by Microsoft Press, plus
-- additional information generously supplied by Kalen Delaney.

set nocount on

-- If no or partial arguments were provided, print usage.
if (@TableName IS NULL) or (@Num_Rows IS NULL) goto usage

-- Verify that @TableName is an object name in the current database, and that it is a user
--    table
declare @ObjId int, @ErrMsg varchar(255)

select @ObjId = object_id(@TableName)
if (@ObjId IS NULL) OR (ObjectProperty (@ObjId, 'IsUserTable') = 0)
   begin
      set @ErrMsg = @TableName + ' is not a user table in the current database'
      goto ErrExit
   end

-- Verify that the estimated number of rows provided by the user is a positive number
if (@Num_Rows <= 0)
   begin
      set @ErrMsg = 'Please enter a positive number for @Num_Rows (estimated number of rows)'
      goto ErrExit
   end

-- Compute the estimated number of pages by:
--   calculating the pages needed to store data (sp_EstTableSizeData)
--   calculating the pages needed to store the clustered index (sp_EstTableSizeCI)
--   calculating the pages needed to store each non-clustered index (sp_EstTableSizeNCI)
--   adding the pages together to arrive at a grand total

-- From the SQL Server 7.0 Books Online section "Nonclustered Indexes":
--   "If the clustered index is not a unique index, SQL Server 7.0 adds an internal
--    value to duplicate keys to make them unique. This value is not visible to users."
-- Thanks to Kalen Delaney for establishing that the length of the internal value is
--    4-bytes (some Microsoft documentation incorrectly states that the length is 8-bytes).
-- Note that this 4-byte "Uniqueifer" is added only to "subsequent" instances of duplicate
--    keys. Since the code you are reading is designed to estimate space requirements, we can
--    not assume that the table given to us is populated with data. (If it were populated
--    with data, then it would make better sense to run the system stored procedure
--    sp_spaceused to determine the actual space requirements of the table.)
--    Since we can not assume that the table is populated, we can not make an accurate,
--    or even an informed guess, as to the distribution of data in the clustered index.
--    Thus, keeping in line with the view that we are creating an upper bound of storage
--    requirements, we assume that for a non-unique clustered index, that every row
--    pointer has this extra 4-byte Uniqueifer.

-- Thanks to Kalen Delaney for establishing that the Uniqueifer, when present uses space
--    not only in the index pages, but also in the data pages.

-- Since the presence of a clustered index, and whether it is unique or not impacts all
--    of the sub-procedures, the determination of its existence is determined up-front.

declare @CI_name as sysname, @Indid as smallint
declare @Has_ClusteredIndex as int, @Has_UniqueClusteredIndex as int

set @Has_ClusteredIndex = ObjectProperty (@ObjId, 'TableHasClustIndex')

if @Has_ClusteredIndex = 1
  begin
    set @CI_name = NULL
    set @Has_UniqueClusteredIndex = 0
  end
else
  begin  -- get the name and index id of the clustered index
    select @CI_name = name, @Indid = indid
    from   sysindexes
    where id = @ObjId
    and   IndexProperty (@Objid, name, 'IsClustered') = 1

    set @Has_UniqueClusteredIndex = IndexProperty (@ObjId, @CI_name, 'IsUnique')
  end


-- Create a temporary table to hold the details on the non-clustered indexes.
create table #ncindexes
(
	IndexName sysname,
	IndexPages int
)

-- Call the supporting sub-procedures
declare @DataPages as int, @CIndex_Row_Size as int, @CIPages as int, @NCIPages as int

exec sp_EstTableSizeData @ObjId, @Num_Rows,
			 @Has_ClusteredIndex, @Has_UniqueClusteredIndex,
			 @DataPages OUTPUT

if @Has_ClusteredIndex = 1
   exec sp_EstTableSizeCI @ObjId, @Num_Rows, @DataPages,
			  @Indid, @Has_UniqueClusteredIndex,
			  @CIndex_Row_Size OUTPUT, @CIPages OUTPUT
else
   begin
      set @CIPages = 0
      set @CIndex_Row_Size = 0
   end

exec sp_EstTableSizeNCI @ObjId, @Num_Rows, @DataPages,
			@CIndex_Row_Size, @Has_UniqueClusteredIndex, @NCIPages OUTPUT

select	'UserTable' = @TableName
select	'Total Pages' = @DataPages + @CIPages + @NCIPages,
	'Data Pages' = @DataPages,
	'Clustered Index Pages' = @CIPages,
	'Non-Clustered Index Pages' = @NCIPages

if exists (select * from #ncindexes)
   begin
      print   ' '
      print   'Non-Clustered Index Details'
      select  'Index Pages' = IndexPages, 'Index Name' = IndexName
      from    #ncindexes
      order by IndexName
   end

RETURN(0)

usage:
print " "
print "Usage:"
print "   exec sp_EstTableSize [@TableName=]userTableName, [@Num_Rows=]estimatedNumberRowsInTable"
print " "
print "Note that the result is an ESTIMATE of the storage to be used by the table."
print " "
print "It should be treated as an upper bound of the storage requirements because it is"
print "assumed that variable length data (varchar, nvarchar, varbinary) will use the"
print "maximum storage on every row. That is, it is assumed that a varchar(100) field"
print "will always contain 100 bytes of data."
print " "
print "If your knowledge of the actual data is such that you may assume that on average"
print "only 20 bytes will be stored in the varchar(100) field, then for a more accurate"
print "estimate, create a second table definition with a varchar(20) field and use that"
print "table as input to sp_EstTableSize."
RETURN (0)


ErrExit:
print " "
print @ErrMsg
RETURN (-1)

Related Snippets