Caveats of the TEXT datatype [SQL Server]

Caveats of the TEXT datatype

Some interesting information on the SQL Server TEXT data type and space used between NULL and an empty string ("").

Given the millions of rows I'm dealing with, a few bytes can make a big difference.

What's nice is the suggested change (setting the "Text in row" on for the table with text and setting the text length way down) can be done without any client code changes.

"...So what does this tell us? I'm thinking that as a best practice, perhaps the text-in-row option should be used for every table with LOB columns, and that it should be set to around 100 bytes. This will still keep row sizes down when larger data is inserted (as it will go off-row), but it will also keep overall IO way down if the amount of rows with LOB data larger than 100 bytes is fairly sparse. "

I agree (once I test this myself :)