5

We have a third party database that has a table with over 300 columns, and in SQL Server 2012 we are getting an error that we are exceeding allowed maximum 8060 bytes. I was wondering if upgrading to SQL Server 2016 will help.

Does SQL Server 2016 also have a limitation of 8060 bytes? Is it possible to have more columns in SQL Server 2016 without encountering the error below?

Error: The table "X" has been created, but 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.

| improve this question | |
  • I can't improve on explanations above, though i'd like to suggest you consider normalization. There are many forms but in properly normalized database designs this row limit is rarely reached. There's so much good reading on this topic but this a good intro... Normalization Article – Ollie Jul 21 '17 at 14:14
12

This limitation relies on the core physical page structure in SQL Server and, no, this hasn't changed in SQL Server 2016 (and won't in SQL Server 2017, either).

There are ways to get around this, though. Top of mind is splitting some larger data out into a separate table. Or just living with the warning message if you know your data won't ever fill all columns on the same row (or you can prevent it).

Or going back to the 3rd party and seeing why they have this and how they prevent actual row problems.

| improve this answer | |
5

This is how SQL Server works and there are limitations to make it perform better as the Page limit is 8KB for all versions of sql server.

Pasting something from msdn that will be helpful :

https://msdn.microsoft.com/en-us/library/ms186981(v=sql.105).aspx

Row-Overflow Data Exceeding 8 KB

A table can contain a maximum of 8,060 bytes per row. In SQL Server 2008, this restriction is relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. The length of each one of these columns must still fall within the limit of 8,000 bytes; however, their combined widths can exceed the 8,060-byte limit. This applies to varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns when they are created and modified, and also to when data is updated or inserted.

| improve this answer | |
  • 1
    If your collective row size exceeds 8060 your row is then split across additional pages, which can decrease performance if those columns need to be returned. The largest column is moved first, although nothing is said about which order columns with identical size are picked. There is a restriction on indexing strategy that Index keys can only be placed on columns that remain on the initial page. This means that if a row is modified & the change takes place in an index column that would be picked for movement, the update will fail... (I agree that normalisation is the way forward & no good reas – simon coleman Nov 7 '19 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.