Overcoming SQL Server's 8060-byte row limit

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.