SQL query to get row size in SQL Server

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'

1_sql query to get row size in sql server

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

   

One Comment on “SQL query to get row size in SQL Server”

  1. 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

Leave a Reply

Your email address will not be published.