about 8KB limit

In SQL2005 with the option ROW_OVERFLOW_DATA the restriction of 8KB by row relaxed for tables that contain varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns. In this case SQL Server use as best the page size, however I wonder what happen when this option is turned off.

For example, If a row size is of 3 KB and I have ROW_OVERFLOW_DATA OFF how SQL Server store my rows? there are about 2 KB of wasted space by page?

There is no such thing called ROW_OVERFLOW_DATA option. You cannot turn it on or off. It is based on the column type. If you have variable length columns, sql server allows you to store rows larger than 8k by pushing variable length column values off-row.

If your row size is 3KB fixed size, you will waste 2KB in each page. There is no way to work around and re-use those 2KB space.

Thanks

Sherry