Each text or image column stores a 16-byte pointer in the data row, with the data type varchar(16). Each text or image column that is initialized requires at least 2K, 1 data page, of storage space.
The text and image columns are designed to store implicit null values, meaning the text pointer in the data row remains null and no text page is initialized for the value, saving 2K of storage space.
If a text or image column is defined to allow null values and the row is created with an INSERT statement that includes NULL for the text or image column, the column is not initialized, and the storage is not allocated.
If a text or image column is changed in any way with UPDATE, including an update to a null value, then the text page is allocated. Inserts or updates that place actual data in a column initialize the page.
The text chains that store text and image data have 112 bytes of overhead per page. Use the following formula to calculate the number of text chain pages that a particular entry will use:
data length/1,800 = number of 2K pages
The result should be rounded up in all cases; for example, a data length of 1,801 bytes requires two 2K pages.