Maximum Row Size with VarChar(MAX)

I'm using SQL Server 2008 R2.

I know if I have a table with 10 varchar(1000) columns and I actually put 1000 characters in each of those columns and try to INSERT that row I'll get an error due to SQL Server's 开发者_StackOverflowrow limitation of 8K. However, if those columns are defined as varchar(max) would I get an error?

If not then what about the following scenario: I have 8 varchar(1000) columns that I max out plus I have 2 varchar(max) columns all on one table with some characters in them. Would I get an error?

I think the answer is no I wouldn't get an error in either case, but I need to be sure.

Your original assumption is incorrect since SQL Server 2005.

The varchar(1000) case would end up with some columns stored offrow so there is no reason to declare 2 of your columns as varchar(max) rather than varchar(1000) just to avoid this non existent issue.

There is a performance overhead to storing data offrow as SQL Server needs to follow pointers from the data page to the off row page so if you expect this to be a common case you might consider vertically partitioning the table in some way.