October 21, 2002 at 12:27 pm
How can I find what amount of a each row is used for a table?
October 21, 2002 at 3:32 pm
sp_spaceused gives an approximate row count (pretty accurate unless your doing loads of inserts when you call it) and the amount of data used, reserved and used for indexes
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 22, 2002 at 7:09 am
I want to find out what size specific rows are. Meaning what amount of data is in there.
October 22, 2002 at 10:18 am
Need to query the len() of the varchar columns. Other data types have their precision listed in select * from information_schema.columns. Have to write something to sum up the values.
Steve Jones
October 23, 2002 at 5:45 am
You actually need to use datalength which will give you the size in bytes of a column, you then need to add 1 bit if it is nullable and some more overhead for row (can't remember what at the mo about 4 bytes i think). Round the number of nullable columns to 8, and the number of bit columns to 8.
so a table with 2 nullable and 2 bit columns
table (col1 int, col2 varchar(10) null, col3 bit, col4 int null, col5 bit)
to get the row size
select datalength(col1)
+ isnull(datalength(col2),0)
+ datalength(col3)
+ isnull(datalength(col4),0)
+ datalength(col5)
+ 1 --2 nullable columns -> 8bits -> 1byte
+ 1 --2 bit columns -> 8bits -> 1byte
from table
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply