How Sql Server 2005 bypasses the 8KB row size limitation

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.

This entry was posted in Sql Development. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

4 Responses to How Sql Server 2005 bypasses the 8KB row size limitation

  1. Diego,

    All you are doing is changing the column from allow nulls to not allow nulls?

  2. Diego Reyes says:

    Hello Ray. Perhaps or Sure you can help me.
    I have a small table with 3 columns. One is an Integer, the second is XML and the last one is varchar(20). Until here no problem, many rows have a data length more than 8060 kb in the xml data column. Here is the problem, this column was nullable and i wanted to alter this column to Not Null but send me an error “Cannot create a row of size 8088 which is greater than the allowable maximum of 8060″….
    What can i do? Thank you a congratulations about your carer.

  3. Good stuff – I had read that back in Beta 1 PDFs but had totally forgot about that… Thanks :)

Leave a Reply