Mike Campbell was sharing his thoughts on using the “Text in Row” option of sp_tableOption system procedure on the SqlAdvice list, so I thought I would expand on it a little.

If you choose to use text, nText or image columns because the data just “might” grow to a size greater than 8060 bytes.   Lookup sp_tableOption and read up on the “text in row” option.

This is a way to have your cake and eat it too when it comes to huge amounts of variability with these columns.

Think of these columns as a place to store your extra large mobile RV.  In my city there is a law that says you cannot store a vehicle larger than say 22 feet on your property.  So you have to store the RV at your local self storage unit or some other place out in the country.

So, in order to use this RV, your buddy, aunt or wife has to drive you to the storage place, pick up your RV and both of you drive it back to the house to load it up for the trip.    BUT.... If your RV is less than 22 feet long, the city is ok with you parking it in your driveway.

If you don’t use the “text in columns” option, the database defaults to the same extra overhead as the RV trip.    The database will store a pointer in the data row and the “data” are stored in a “collection of pages”   Which means that if your text is only 2k bytes you have the added overhead of looking up the pointer and traveling to the “other pages” and retrieving the data.   IE, a trip to the Self Storage Yard..

So, if you estimate that most of your data will be smaller, let's say 2048 bytes, and you want to keep the potential for a 2GB possibility, use the “text in column” to keep it most of your data in the same data row / page.

But, be careful.   This performance gain is only available IF you have space left in the data row.    So if all your other columns add up to 7k and your nText column is 2048, you get to go to the storage yard anyway <grin>