Q
Problem solve Get help with specific problems with your technologies, process and projects.

Overcoming SQL Server's 8060-byte row limit

SQL Server 2005 expert Adam Machanic discusses an important feature called row overflow, which greatly relaxes the 8060-byte restriction.

Can SQL Server 2005 handle rows with more than 8060 bytes of data?

SQL Server's 8060-byte row limit has been quite a thorn in the side of developers working with the DBMS. In SQL...

Server 7 and 2000, the only way to get around this limit was to either use large object types (TEXT/NTEXT/IMAGE) or to split up data into multiple tables—neither option very friendly.

SQL Server 2005 introduces an important feature called row overflow, which greatly relaxes the 8060-byte restriction. In short: It is now possible to insert rows with more than 8060 bytes of data, as long as the columns that cause the row size to exceed 8060 bytes are of varying types. Should a row exceed 8060 bytes, the data for varying columns (typed as VARCHAR, NVARCHAR, or VARBINARY) will be automatically moved off-row into the large object area, rather than terminating the data modification.

Note that table size is still limited by fixed-length types, as well as internal headers and row pointers. This means that you still cannot create a table with a row size of greater than 8060 bytes if that table is comprised of all numeric columns, for instance. In addition, each column that is moved off-row will occupy a 24-byte on-row pointer. You must make sure when creating large tables that the pointers will not cause rows to overflow the 8060-byte limit, or data modifications may be terminated as a result of too much data in row.

 


Do you have comments on this Ask the Expert Q&A? Let us know.
This was last published in December 2005

Dig Deeper on Microsoft SQL Server 2005

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close