Text in Row vs. Varchar or Text

What's the difference between using the text in row storage feature and using the varchar data type, and which will buy me better response time when I'm storing and retrieving arbitrarily long character strings?

If you're running SQL Server releases earlier than SQL Server 2000, you have two choices for storing and retrieving arbitrarily long character strings. If you choose the varchar data type, you might have to break the data across several rows to avoid hitting the 8000-character limit in SQL Server 7.0 or the 256-character limit in SQL Server 6.5. Alternatively, you can choose the text data type, which stores an in-row pointer to separate text pages in which the data is stored. This storage method requires two I/Os for a retrieval—one I/O for the pointer and at least one other I/O for the first text page. A double I/O slows down the scans that filter on the value of the text column.

SQL Server 2000 introduced the text in row concept, which helps you avoid the text data type's double I/O (and the potential seek/rotate latency) by letting you specify the part of the text data that you want to store in the data row. Using this feature is a good idea when you're storing small text files that fit on one page or when you're looking at the head page of a text file to help you decide whether to retrieve the whole file. Note, however, that using text in row reduces the rows-per-page density, and density reduction increases the number of I/Os necessary for SQL Server to scan the data. Therefore, if you're scanning the table on another column, you might consider using an index.

You should also be aware that text columns have limitations that varchar columns don't have. For example, you can't index them, and you can't use them with some cursor types. These limitations might require you to use special functions such as Updatetext, WriteText, and ADO's stream interfaces. We would use text in row only if data in some of the rows needed more space than varchar or nvarchar allows.