This post originated from an RSS feed registered with .NET Buzz by Raymond Lewallen. Original Post: How Sql Server 2005 bypasses the 8KB row size limitation Feed Title: Raymond Lewallen Feed URL: /error.htm?aspxerrorpath=/blogs/raymond.lewallen/rss.aspx Feed Description: Patterns and Practices, OOP, .Net and Sql Latest .NET Buzz Posts Latest .NET Buzz Posts by Raymond Lewallen Latest Posts From Raymond Lewallen

Here I talked about row size limitations and using varchar versus nvarchar data in Sql Server.  Now, lets look at what you can do in Sql Server 2005 that allows you to surpase the 8KB row size limit.

Sql Server 2005 still adheres to the 8K page size.  But now, you are allowed to have rows that exceed that limit.  Individual columns still must adhere to 8K limits.  This means you can have a table defined as varchar(5000), varchar(5000), but you cannot have a table defined as varchar(10000).  The same applies with nvarchar, which would be a table with nvarchar(3000), nvarchar(3000), but you’re not allowed nvarchar(5000).  What happens in Sql Server 2005 is that when combinations of varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns exceeds the 8K limit, the column for a record with the largest size is moved to another page in what is called a ROW_OVERFLOW_DATA allocation unit, again like Text and Image data, keeping a 24 byte pointer in the original data page, which is the IN_ROW_DATA allocation unit.

This all happens behind the scenes, but understand the performance consequences of this happening.  When you update a row with data that will cause the row to exceed the 8K limit, part of that row is moved to a new page.  If you update a row that is split between pages and it now fits within the 8K limit, this may cause the split row to be merged back into the original data page, both of which cause performance degredation.  Querying data or performing joins on data that have data allocated in the ROW_OVERFLOW_DATA also slows performance because these records are processed synchronously.  If you were to normalize that data so that the data is split between tables and use a JOIN instead of using rows larger than 8K, this speeds your performance back up because JOINS are asynchronous operations.