Below is a SQL query to find row size. The query uses DATALENGTH function, which returns the number of bytes used to represent a column data. A row may consist of fixed, variable data types. A varchar is a variable datatype which means that a varchar(50) column may contain a value with only 20 characters. The DATALENGTH function comes handy here.
IF object_id('sp_GetRowSize') is not null
drop procedure sp_GetRowSize
GO
CREATE procedure sp_GetRowSize(@Tablename varchar(100),@pkcol varchar(100))
AS
BEGIN
declare @dynamicsql varchar(1000)
-- A @pkcol can be used to identify max/min length row
set @dynamicsql = 'select ' + @PkCol +' , (0'
-- traverse each record and calculate the datalength
select @dynamicsql = @dynamicsql + ' + isnull(datalength(' + name + '), 1)'
from syscolumns where id = object_id(@Tablename)
set @dynamicsql = @dynamicsql + ') as rowsize from ' + @Tablename + ' order by AddressID'
exec (@dynamicsql)
END
The above query creates a procedure. The procedure accepts two parameters @Tablename and @pkcol. The tablename is the name of the table you want to find the row size for and the @pkcol is the either the id column of the primary key column which identifies a row uniquely.
-- Execute the procedure sp_getrowsize 'Person.Address','AddressID'
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
I know this is an old post, but there’s an easier way that doesn’t include dynamic sql:
select
SomeOtherColumn,
Bytes = datalength((select x.* from (values(null))data(bar) for xml auto))
from Table x