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.