July 12, 2002 at 8:14 am
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
July 12, 2002 at 8:33 am
The system stored procedure sp_spaceused can be used for this purpose. For instance:
USE Northwind
GO
EXEC sp_spaceused 'Orders'
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 12, 2002 at 8:34 am
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy