SQL Server 2016 Maximum Bytes/Row

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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