Dealing with the large object (LOB) data types (text, ntext, and image) is a little more complex than dealing with the other types, as you probably know. When you throw in the text in row option, which specifies that small amounts of data can be stored in the data row instead of on separate pages, you have even more details to be concerned about. As I mentioned last month in "Text Pointers" (InstantDoc ID 27520), there's a crucial difference in locking behavior between normal LOB data and LOB data stored in a table that has the text in row option enabled. If a table isn't enabled for in-row text storage, SQL Server holds no row lock for the text pointer, and another process might invalidate the text pointer before you can use it. The only way to lock the data row and protect the text pointer is to raise the isolation level to at least REPEATABLE READ. The drawback of using the isolation level to prolong the lock is that you can't release the lock earlier than the end of the transaction.
When you're working with LOB data in a table that has the text in row option enabled, you can't acquire a text pointer except in a user-defined transaction. You must then use the text pointer in the same transaction because the pointer is invalidated automatically at the end of the transaction, and once the pointer is invalid, SQL Server releases the lock on the row. However, the text pointer can be invalidated in other ways. Let's examine some of the ways to invalidate the pointer and see how you can use DBCC commands to view text data to determine whether it's on the data page or on its own special LOB pages.
Releasing the Lock
In a table that has text in row enabled, you have control over when the lock is released—it's released whenever the text pointer becomes invalid. As I mentioned, the end of a transaction automatically invalidates a text pointer, but you can force SQL Server to invalidate the text pointer and release the lock before the end of the transaction. Note that the rules for invalidating text pointers apply to all rows in a table that have text in row enabled, even the rows in which the text data is too big to fit in the row. SQL Server also invalidates text pointers for tables that have text in row enabled when you perform the following DDL operations on the table:
- CREATE CLUSTERED INDEX
- DROP CLUSTERED INDEX
- ALTER TABLE
- DROP TABLE
- TRUNCATE TABLE
- sp_tableoption with the text in row option
- sp_indexoption
You might need to force invalidation of text pointers because SQL Server sets an upper limit on the number of pointers for in-row text data. SQL Server maintains internal memory for each valid text pointer, and it needs to limit the amount of memory that's used in this way. You can have up to 1024 valid text pointers per transaction per database. If your transaction spans two databases, you can have 1024 text pointers in each database. Within a transaction, obtaining a text pointer twice on the same LOB data gives you the same text pointer structure and doesn't count against your limit of 1024. SQL Server doesn't limit the number of pointers you can have for LOB data in a table that doesn't have text in row enabled.
The code in Listing 1, page 22, shows an example, based on code I used in "Text Pointers," of using ALTER TABLE, which invalidates the text pointer. Be sure to drop the t1 table first if it already exists. The code in Listing 1 creates a table that has a text column, then enables the text in row option for the table. The code then inserts two rows into the table: one with data that fits into the supplied text in row limit of 50 bytes and one with text data that's too big. The code then starts a transaction, retrieves a text pointer, then uses the text pointer to access text data. Inside the same transaction, the code executes an ALTER TABLE statement; a second attempt to use the text pointer results in an error, demonstrating that the text pointer was invalid. Note that you must submit the whole transaction as one batch because the local variable holding the value of the text pointer exists only for the duration of the batch.
Prev. page
[1]
2
next page 
