That's actually not the real problem that you're going to/are having. The real problem is that XML is a LOB datatype that's just about the same as VARCHAR(MAX) (as an example). This issue started way back in 2005 when MS came out with the new LOB datatypes... they defaulted them to "In Row" instead of "Out of Row".
There are three serious problems that causes...
- Totally unnecessary extreme bloating of Clustered Indexes (and Heaps)
- Massive amounts of page splits in Clustered Indexes and forwarded rows in Heaps due to "ExpAnsive" Updates
- Potentially (but frequently) huge amounts of wasted space in Clustered Indexes/Heaps in the form of what I call "Trapped Short Rows" where pages can permanently have ultra low page densities even as low as 1% and less.
That means that the NULLs you're concerned about are also a part of the problem because they are part of what enables all the problems above to occur.
To summarize the fix for all that, you have to set the table option to have LOBs out of row and you have to default the LOB column to a single space so that the pointer that the 16 byte pointer will be created so that no "ExpAnsive" Updates will occur in the future. It's usually a very worthwhile investment in additional bytes. If the table already has data in it, you'll also have to do an "in-place" update on the column (update the column to itself) in order to get the existing data to move out of row. Any new data will be moved auto-magically.
And, no... there is no database wide setting for the table out of row option. You have to do it for each table when you design the table or when you want to update a table to have the option.
There are also a couple of caveats in answering your question. If the other variable width columns that are physically to the right of a null variable width column are also null, then the column will take no bytes. If, however, any of the columns to the right are not null, then the column in question will contain 2 bytes even if null to indicate that the length of the column is zero. That also doesn't include any bits in nulllable columns fields of the row header. It also isn't documented anywhere that that's what happens. I've only recently discovered it and tested it (like 3 weeks ago) as a proof.