SQL Server throws error of “Cannot create a row of size 8180 which is greater than the allowable maximum row size of 8060” in Insert/Update statements, This means your SQL statement having fields which are LOB data types and exceeds the limit of row size which is 8086 bytes for physical data store in database how ever It does not have issue with Text and Images as they are stored separately.
While digging into this issue, I found that in my table there were over 250 columns and more then 60% was having type as NVARCHAR or VARCHAR with size between 200 to 4000. SQL Server creates pointers a linked list and stored somewhere else, so table that contains multiple VARCHAR field will always be 8k part on disk, because VARCHAR fields are stored inline in the record, this is the reason why my statement get fails, as my table contains over 60% type which is NVARCHAR or VARCHAR.
That means we have to change default behavior of storing large value in-row; SQL Sever provides functionality to store such value out of row, but this only works for large value type. A system procedure sp_tableoption in SQL server system allow us to change this behavior, there are two option provided by this procedure, which are ‘large value types out of row’ and ‘text in row’. First option used for large value types and second option used for large object. As article written on msdn, the ‘text in row’ is going to remove in future version of SQL Server. So its advisable to use ‘large value types out of row’.
So we figured out what is the reason and also we found the solution. Following is the syntax to store large value type out-of-row.
sp_tableoption N'YourTableName', 'large value types out of row', 'ON'
You have to specify table name on which you have to do this settings, this option may give you following error
Cannot use table option LARGE VALUE TYPES OUT OF ROW on a user table that does not have any of large value types varchar(max), nvarchar(max), varbinary(max), xml or large CLR type columns in it. This option can be applied to tables having large values computed column that are persisted.
Its very clear from error that you have to have single column with max or xml or large CLR types. So first we have to change our table accordingly by making required field to use any of given type and then you can insert more then 8060 bytes.
following is simple command to disable out-of-row option.
sp_tableoption N'Categories', 'large value types out of row', 'OFF'
This solution works fine for me, for more information on sp_tableoption and effects/side effects from this article.