Sparse Varchar(max)

Question

I have a Microsoft SQL Server 2008 table with a Varchar(max) field that is rarely used. Would it make more sense to move that field into another table and point to this table?

Or if a Varchar(max) field contains null, does it not take up a whole lot of room?

Answer 1

No. SQL Server already uses a separate allocation unit for BLOB types, see Table and Index Organization. A NULL value occupies only one bit in a row, see Anatomy of a Record.

You will waste more space by creating a separate table and will be significantly more expensive to check if the column is NULL or has a value. Besides the column values may be optimized and pulled in-row for small values, if they fit, offering significant faster access. Overall, don't try to micro-optimize something that the engine is already pushing the envelope to make it as efficient as possible....

If the table is sparse (meaning it has many (hundreds, thousands) columns from which many are NULL on each row) then you could use sparse columns.