Its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

Question

Hi,

I have a table and its having 450 columns. i am using sql server 2008.

Almost all the columns have datatype varchar(50),varchar(3000) and varchar(max).

i am getting below error message, when i run the table creation script.

Its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

Is anyone can suggest me good approach, which can overcome this problem?

Thanks,

Maggy!

Answer 1

Do you really need all columns varchar(50),varchar(3000) and varchar(max)? 

Is that possible to split the table?

http://rusanu.com/2012/02/16/adding-a-nullable-column-can-update-the-entire-table/

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 2

It's not an error messge, but a warning that you may get errors when you try to insert or update data into the table.

The way to deal with this warning is to use a proper database design. 450 columns in a table is quite a lot. Particularly if you have followed the idea that each column is supposed to model a unique attribute of the entity.

Last time I saw a table with so many columns and that produced this warning, that table was grossly maldesigned. They had lots of LineItem1, LineItem2 etc, and there were also columns that all had the same information in different language. That table should have been some 20 tables or so.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Well since there's nothing you can do to change the 8060 byte limit I guess your options are to either fix the underlying problem or cross your fingers, ignore the error and hope for the best.

450 columns does seem somewhat excessive to me, especially considering that allows you an average column width of only 18 characters, without allowing for the varchar(max) columns which can shift their data outside the main 8k page if required. If possible I'd definitely want to split the data into multiple tables like Uri suggests.

If only some of the columns are going to be used in each row then it might not be too bad (SharePoint does something similar with it's data), but then you're faced with the problem of how you're going to trap and handle it in your application if you do go over the limit.

I'd want to do more reading on the matter to be sure, but changing some of those varchar(3000) columns to varchar(max) might not hurt, since as I understand it the data remains in the main table while there's space within the 8060 byte limit, so if you did end up with a load of data in several of them you'd be OK, but I get the impression that there are limitations on what you can do with those columns, and a significant overhead involved as well.

Answer 4

>Almost all the columns have datatype varchar(50),varchar(3000) and varchar(max).

Change all to varchar(max) and check if size below 8060.

As suggested above, better design maybe in order.  For additional assistance, post DDL.

---

Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 5

To echo what Erland said, in 30+ years of doing databases, the greatest number of columns I have seen was a little over 120. It was a medical record for allergy tests where they put a grid on a patient's back and stuck him with needles to see what would make him react. The columns were SMALLINT scores.  VARCHAR(50) is usually a lazy noob using the default size from Microsoft products (lazy noobs in other products get VARCHAR(255) from their vendor  defaults). Using VARCHAR(MAX) is a pure dialect that will not port, fills with garbage data and is a good sign that no effort was made to think about the logical design of the data elements. Exact what are you modeling in this table? 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 6

As others have said it's just a warning, but it's also a warning that your schema is probably not very well designed.

But never fear, SQL Server has options for you!

Varchar(max) will allow the values to be stored in separate pages and avoid the 8k limit, so just change those varchar(3000) to varchar(max) and you might be done.

Then read up on the wide tables / sparse column feature:

http://msdn.microsoft.com/en-us/library/ms186986(v=SQL.105).aspx

I worked on one system a few years back where we were pushing on the 1,024 column limit, the table being a denormalized join of everything else, sort of a localized data warehouse inside the main OLTP database.  Was that a good design?  Well, ... up to a certain scope, yes, I think it was, and it worked well.  But this archaic 8k maximum page size was a problem.  And we're still stuck with it.  But with varchar(max) and wide tables it doesn't have to be a real problem for you.

Josh