SQL 2K5 introduces a new behavior row-overflow, which enable the combined row size exceed 8,060 bytes per row limit for varchar, nvarchar, varbinary, sql_variant or CLR user-defined type columns. For more details, check out BOL: http://msdn2.microsoft.com/en-us/library/ms186981.aspx
What's this mean to the database professionals?
Good news for data modelers is you don't have to worry about the error message 'Cannot create a row of size xxxx which is greater than the allowable maximum of 8060' when you try to update/insert a record whose total length of all columns exceeds 8060.
Bad news for DBAs, you may encounter a performance degrading because SQL 2K5 still maintains a limit of 8 KB per page, and for the rows exceed 8060, the 'Row-Overflow' will kick in and move large records to another page dynamically, and perhaps, multiple I/Os operations will be needed in order to retrieve a single row of data.
The suggestion is to normalize the table which will likely cause row-overflow so that some columns are moved to another table.
Two things need to clarify are:
-
A single of varchar, nvarchar, varbinary, sql_variant, and CLR user-defined type columns will still has the limit of 8000 bytes, although the entire row can exceed this restriction.
-
Large-value data types such as varchar(max), nvarchar(max), and varbinary(max) can store up to 2^31-1 bytes of data, XML type store 2GB data. The sp_tableoption system stored procedure with the “large value types out for row” option set to OFF can force the large data to be always stored out of row. You should only do this when you are very much sure the data in that column will exceed 8000 bytes – that is the size of a data page. Check out this article for Large-Value Data Types http://www.databasejournal.com/features/mssql/article.php/3632906