How big can a SQL Server row be before it's a problem?

Question

Occasionally I run into this limitation using SQL Server 2000 that a row size can not exceed 8K bytes. SQL Server 2000 isn't really state of the art, but it's still in production code and because some tables are denormalized that's a problem.

However, this seems to be a non issue with SQL Server 2005. At least, it won't complain that row sizes are bigger than 8K, but what happens instead and why was this a problem in SQL Server 2000?

Do I need to care about my rows growing? Should I try and avoid large rows? Are varchar(max) and varbinary(max) a solution or expensive, in terms of size in database and/or CPU time? Why do I care at all about specifying the length of a particular column, when it seems like it's just a matter of time before someones going to hit that upper limit?

Answer 1

Read up on SQL Server 2005 row size limit here:

How Sql Server 2005 bypasses the 8KB row size limitation

SQL Server will split the row data if it's greater than 8K and store the superfluous data into a second data page using a pointer to it in the original one. This will impact performance on queries and joins.

Answer 2

There is still a row size limit - the minimum row size cannot exceed 8060 bytes

CREATE TABLE Table1 (
    col1 char(2000),
    col2 char(2000),
    col3 char(2000),
    col4 char(2000),
    col5 char(2000)
);

Creating or altering table 'Table1' failed because the minimum row size would be
10007, including 7 bytes of internal overhead. This exceeds the maximum allowable
table row size of 8060 bytes.

When you use varchar(MAX) the strings are not stored inside the row but in another location so in this way you can store more than 8060 bytes. Storing lots of large strings is of course expensive. Just do the calculations and you can see that it will quickly consume large amounts of disk space. But if you do need to store large string then it's OK to do that. The database can handle it.