Sql-server – SQL Server 2016 Maximum Bytes/Row

sql serversql-server-2016

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.

Best Answer

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.

Related Solutions

SQL Server VARCHAR Column Width – Best Practices

The question might be better stated as:

"What is the advantage of over-specifying the maximum length of a variable-length column?"

In general, there is little advantage, and several disadvantages as the various linked answers point out. Aside from any other concerns, consider that SQL Server is not open-source: there are many 'magic numbers' and heuristics applied based on the information provided to the system. Without source code access, we could never be entirely sure what the impact of this practice might be.

In some cases, where the average length of a column is significantly higher than the 50% assumed by SQL Server when calculating sort/hash memory grants, you may see a performance improvement by over-specifying the maximum length. This is a dubious workaround, and should probably only be applied by an explicit CAST or CONVERT (with comments!) rather than changing the base column definition. Sometimes, it will be preferable to rewrite the query to sort keys instead of whole rows anyway.

Where the maximum row size might exceed the in-row limit (even if no rows actually do), deleting rows can cause unexpected page-splitting if a trigger is present. Updates may also lead to fragmentation via the same mechanism.

SQL Server does a pretty good job in most cases where it is provided with good, accurate information from metadata. Compromising this principle for 'convenience' seems unwise to me. A sensible approach is to choose a maximum length value that is reasonable according to the actual data to be stored, and any foreseeable changes.

Sql-server – “Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060” while altering the table

If you have previously removed or modified columns on this table, you may need to reclaim space before this operation will succeed. SQL Server does not always/usually immediately reclaim space for dropped or altered columns.

If the previous operations were all removals (or alterations) of variable-length columns, issuing DBCC CLEANTABLE ought to be sufficient. Otherwise, you will need to rebuild the table. You can do this by rebuilding the clustered index:

ALTER INDEX PK_table
ON dbo.table
REBUILD 
WITH (ONLINE = ON); -- Enterprise only, optional

Best Answer

Related Solutions

SQL Server VARCHAR Column Width – Best Practices

Sql-server – “Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060” while altering the table

Related Question