VERY Wide Rows, 8060 Bytes Row Size Limit && SPARSE Columns

Hi,
I��m trying to create a VERY wide table, with 1,000 columns of type varchar(MAX), nullable.
The CREATE TABLE statement (both in SQL 2005 & 2008), gives the following warning:


Warning: The table "WIDE_TABLE" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

When I insert data into the table, filling all columns with small, 10-byte string values, I get the following error:

Msg 50000, Level 16, State 1, Procedure sp_pivot, Line 118

Cannot create a row of size 15034 which is greater than the allowable maximum of 8060.

I��d like to verify this observation: each row is created with 2000 bytes of offset data (2 byte * 1000 columns), 125 bytes for null bitmap (1000 columns / 8 bits) and some more ��wasted�? row information. This leaves less than 6K for the data itself. But since not all columns can fit within the page, forwarding pointers in the row need to be created, 24 byte per column, which very quickly add up to more than 8K, thus the error. So the 8K limit is met for much less columns than the max 1024 column restriction.

Furthermore, in SQL 2008, SPARSE columns will not solve the problem (maybe save some ��metadata�? space in case the columns are null, but if not, I��m with the same problem again, or even worse, since now each value takes more storage space. The max 30,000 columns in 2008 is only for cases where the column values are really sparse��

Is this the right observation? if so, is there a workaround besides splitting to multiple tables?

Thanks,

Aviv.