Using text in row to Store text, ntext, and image Values
Usually, text, ntext, or image strings are large, a maximum of 2GB, character or binary strings stored outside a data row. The data row contains only a 16-byte text pointer that points to the root node of a tree built of internal pointers that map the pages in which the string fragments are stored.
With SQL Server, you can store small to medium text, ntext, and image values in a data row, thereby increasing the speed of queries accessing these values.
When the text, ntext, or image string is stored in the data row, SQL Server does not have to access a separate page or set of pages to read or write the string. This makes reading and writing the text, ntext, or image in-row strings about as fast as reading or writing varchar, nvarchar, or varbinary strings.
To store text, ntext, or image strings in the data row, enable the text in row option using the sp_tableoption stored procedure.
sp_tableoption N'MyTable', 'text in row', 'ON';
Optionally, you can specify a maximum limit, from 24 through 7000 bytes, for the length of a text, ntext, and image string stored in a data row:
sp_tableoption N'MyTable', 'text in row', '1000';
If you specify 'ON' instead of a specific limit, the limit defaults to 256 bytes. This default value provides most of the performance benefits: It is large enough to ensure that small strings and the root text pointers can be stored in the rows but not so large that it decreases the rows per page enough to affect performance.
Although in general, you should not set the value below 72, you also should not set the value too high, especially for tables where most statements do not reference the text, ntext, and image columns or there are multiple text, ntext, and image columns.
You can also use sp_tableoption to turn the option off by specifying an option value of either 'OFF' or 0:
sp_tableoption N'MyTable', 'text in row', 'OFF';