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
-
- declare @table nvarchar(128);
- declare @idcol nvarchar(128);
- declare @sql nvarchar(max);
-
-
- set @table = '[Person].[AddressType]'
- set @idcol = 'AddressTypeID, Name'
- set @sql = 'select ' + @idcol + ' , (0'
-
-
- 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'
-
-
- 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 !!