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.
Dig Deeper on Microsoft SQL Server 2005
Related Q&A from Adam Machanic
Migrating to SQL Server 2005 from SQL Server 2000 is a hefty feat when compared to upgrading from 7.0 to SQL Server 2000. Site expert Adam Machanic ... Continue Reading
Multiple readers can sometimes read the same row simultaneously causing a false result. SQL Server 2005 expert Adam Machanic suggests modifying the ... Continue Reading
SQL Server 2005 and T-SQL do have differences. The advantages and disadvantages would depend user environment as described by SQL Server 2005 expert... Continue Reading