Calculating Table Size

• kroger

  Grasshopper

  Points: 15

  More actions

  July 12, 2002 at 8:14 am

  #57851

  I need to calculate the table size based on the query.

  For example,

  select * from Table

  where ID='3'

  Can anyone help me

  yjk

  
  yjk

• K. Brian Kelley

  SSC Guru

  Points: 114562

  More actions

  July 12, 2002 at 8:33 am

  #432607

  The system stored procedure sp_spaceused can be used for this purpose. For instance:

  
  
  USE Northwind
  
  GO
  
  EXEC sp_spaceused 'Orders'
  
  

  K. Brian Kelley

  bkelley@sqlservercentral.com

  http://www.sqlservercentral.com/columnists/bkelley/

  K. Brian Kelley
  @kbriankelley

• Antares686

  SSC Guru

  Points: 125444

  More actions

  July 12, 2002 at 8:34 am

  #432608

  This is assuming you want to know the output table size.

  Here is the math I use, it is rough but I believe this is correct.

  DatalengthPerRow * NumberOfRows = TotalBytesOfData

  8000bytesPerPage / DatalengthPerRow = TotalNumberOfRowsPerPageRoundDown

  TotalNumberOfRowsPerPage * DatalengthPerRow = TotalNumberOfBytesPerPageStored

  TotalBitesOfData / TotalNumberOfBytesPerPageStored = TotalNumberOfPagesRoundedUp

  TotalNumberOfPagesRoundedUp * 8k = TotalSizeIn_kilobytes

  Ex.

  CREATE tblX (

  id int NOT NULL,

  colx int NULL,

  coly varchar(50)

  )

  In this case each int is 4 and the varchar is 50 so total is 58. Now the query will return 5000.

  So 58 * 5000 = 290000 bytes

  so to get size in 8 k pages do 8000 / 58 = 137 with round down

  now get amount of data per page 137 * 28 = 7946 bytes

  so 290000 / 7946 = 36.496.. or round up if any remainder 37 8k pages

  or 37 * 8 = 296kilobytes

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  Edited by - antares686 on 07/12/2002 08:40:02 AM