Calculate Row Size of a Table in SQLServer

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

1. -- Declaring variables   
2. declare @table nvarchar(128);  
3. declare @idcol nvarchar(128);  
4. declare @sql nvarchar(max);  
5.    
6. --initialize those two values  
7. set @table = '[Person].[AddressType]'  
8. set @idcol = 'AddressTypeID, Name'  
9. set @sql = 'select ' + @idcol + ' , (0'  
10.    
11. -- This select statement collects all columns of a table and calculate datalength  
12. select @sql = @sql + ' + isnull(datalength(' + name + '), 1)'  
13. from sys.columns where object_id = object_id(@table)  
14. set @sql = @sql + ') as RowSize from ' + @table + ' order by rowsize desc'  
15.    
16. -- Execute sql query   
17. exec (@sql)  

Result:

 

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