Calculate Row Size of a Table in SQLServer

This Blog will describe how to calculate row size of a table in SQL Server.

Today I came across with an error with "Row-Overflow Data Exceeding 8 KB" when trying to update the value in a row.

 

Reason

MS SQL server allows only 8060 bytes of data max to be stored in a row. Hence your row size will always be <= 8060. But it is relaxed when a table contains varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type colums.

Please see below query how to figure out which row takes how much space:

 

Query

-- Declaring variables
declare @table nvarchar(128);
declare @idcol nvarchar(128);
declare @sql nvarchar(max);

--initialize those two values
set @table = '[Person].[AddressType]'
set @idcol = 'AddressTypeID, Name'
set @sql = 'select ' + @idcol + ' , (0'

-- This select statement collects all columns of a table and calculate datalength
select @sql = @sql + ' + isnull(datalength(' + name + '), 1)'
from sys.columns where object_id = object_id(@table)
set @sql = @sql + ') as RowSize from ' + @table + ' order by rowsize desc'

-- Execute sql query
exec (@sql)

Result: 

 

Above SQL query just collects all the columns present in a table and summing up the data size using datalength().

 

 

Happy Coding !!