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().
 
 
Happy Coding !!
Ebook Download
View all
Learn
View all