Managing ntext, text, and image Data

Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use varchar(max), nvarchar(max), and varbinary(max) data types instead. For more information, see Using Large-Value Data Types.

The Microsoft SQL Server 2005 ntext, text, and image data types are capable of holding extremely large amounts of data, up to 2 GB, in a single value. A single data value is typically larger than can be retrieved by an application in one step; some values may be larger than the virtual memory available on the client. Therefore, special steps are usually needed to retrieve these values.

If an ntext, text, and image data value is no longer than a Unicode, 4,000 characters; character, 8,000 characters; or binary string, 8,000 bytes, the value can be referenced in SELECT, UPDATE, and INSERT statements much the same way as the smaller data types. For example, an ntext column with a short value can be referenced in a SELECT statement select list the same way an nvarchar column is referenced. Some restrictions that must be observed, such as not being able to directly reference an ntext, text, or image column in a WHERE clause. These columns can be included in a WHERE clause as parameters of a function that returns another data type, such as ISNULL, SUBSTRING or PATINDEX, or in an IS NULL, IS NOT NULL, or LIKE expression.

Handling Larger Data Values

When the ntext, text, and image data values get larger, however, they must be handled on a block-by-block basis. Both Transact-SQL and the database APIs contain functions that allow applications to work with ntext, text, and image data block by block.

The database APIs follow a common pattern in the ways they handle long ntext, text, and image columns:

• To read a long column, the application simply includes the ntext, text, or image column in a select list, and then binds the column to a program variable large enough to hold a reasonable block of the data. The application then executes the statement and uses an API function or method to retrieve the data into the bound variable one block at a time.
• To write a long column, the application executes an INSERT or UPDATE statement with a parameter marker (?) in the place of the value to be placed in the ntext, text, or image column. The parameter marker (or parameter in the case of ADO) is bound to a program variable large enough to hold the blocks of data. The application goes into a loop where it first moves the next set of data into the bound variable, and then calls an API function or method to write that block of data. This is repeated until the entire data value has been sent.

Using text in row

In SQL Server 2005, users can enable a text in row option on a table so it could store text, ntext, or image data in its data row.

To enable the option, execute the sp_tableoption stored procedure, specifying text in row as the option name and on as the option value. The default maximum size that can be stored in a row for a BLOB, a binary large object such as text, ntext, or image data, is 256 bytes, but values may range from 24 through 7000. To specify a maximum size that is not the default, specify an integer within the range as the option value.

text, ntext, or image strings are stored in the data row if the following conditions apply:

• text in row is enabled.
• The length of the string is shorter than the limit specified in @OptionValue
• There is enough space available in the data row.

When BLOB strings are stored in the data row, reading and writing the text, ntext, or image strings can be as fast as reading or writing character and binary strings. SQL Server does not have to access separate pages to read or write the BLOB string.

If a text, ntext, or image string is larger than the specified limit or the available space in the row, pointers are stored in the row instead. The conditions for storing the BLOB strings in the row still apply though: There must be enough space in the data row to hold the pointers.

For more information, see sp_tableoption (Transact-SQL).

Using text pointers

Unless the text in row option is specified, text, ntext, or image strings are stored outside a data row; only the text pointers to these strings reside in the data rows. Text pointers point to the root node of a tree built of internal pointers that map to the pages in which string fragments, of text, ntext, and image data, are actually stored.

In row text pointers in SQL Server 2000 are different from the text pointers in earlier versions of SQL Server. In row text pointers behave like file handles for BLOB data; earlier text pointers function like addresses to the BLOB data. Thus, when using in row text pointers, keep in mind the following characteristics:

Important

Although in-row text is allowed in a cursor, an in-row text pointer is not. SQL Server returns error 328 if you attempt to declare a cursor that contains an in-row text pointer.

1. Number
   A maximum of 1024 active in row text pointers are allowed per transaction per database.

2. Locking
   When a user obtains an active text pointer, SQL Server 2000 locks the data row and ensures no other user modifies or deletes the row while the first user has the text pointer. The lock is released when the text pointer becomes invalid. To invalidate a text pointer, use sp_invalidate_textptr (Transact-SQL).
   A text pointer cannot be used to update BLOB values when the isolation level of the transaction is read uncommitted, or the database is in read-only mode.
   SQL Server 2000 does not lock the data row if the database is in single-user mode.
   To illustrate, given the following table:

   CREATE TABLE t1 (c1 int, c2 text)
   EXEC sp_tableoption 't1', 'text in row', 'on'
   INSERT t1 VALUES ('1', 'a')
   

   The following transaction will succeed:

   INSERT t1 VALUES ('1','This is text.')
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   GO
   BEGIN TRAN
   DECLARE @ptr varbinary(16)
   SELECT @ptr = textptr(c2)
   FROM t1
   WHERE c1 = 1
   READTEXT t1.c2 @ptr 0 5
   COMMIT TRAN
   GO
   

   The following transaction will fail:

   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   GO
   BEGIN TRAN
   DECLARE @ptr varbinary(16)
   SELECT @ptr = textptr(c2)
   FROM t1
   WHERE c1 = 1
   WRITETEXT t1.c2 @ptr 'xx'
   COMMIT TRAN
   GO
   

3. Duration
   In row text pointers are valid only within a transaction. When a transaction is committed, the text pointer becomes invalid.
   Within a transaction, in row text pointers can be invalidated when any of the following actions take place:

   • The session ends.
   • The data row is deleted in the same transaction. (Other transactions cannot delete a data row because of the lock obtained on it.)
   • The schema of a table in which the text pointer resides is changed. Schema-changing actions that invalidate text pointers include: creating or dropping clustered index, altering or dropping the table, truncating the table, changing the text in row option through sp_tableoption, and executing sp_indexoption.

   Using the earlier example, the following script would work in earlier versions of SQL Server, but will generate an error in SQL Server 2000.

   DECLARE @ptrval varbinary(16)
   PRINT 'get error here'
   SELECT @ptrval = TEXTPTR(c2)
   FROM t1
   WHERE c1 = 1
   READTEXT t1.c2 @ptrval 0 1
   

   In SQL Server 2000, the in row text pointer must be used inside a transaction:

   BEGIN TRAN
   DECLARE @ptrval varbinary(16)
   SELECT @ptrval = TEXTPTR(c2)
   FROM t1
   WHERE c1 = 1
   READTEXT t1.c2 @ptrval 0 1
   COMMIT
   

4. NULL text
   You can get an in row text pointer on NULL text that is generated by INSERT. Previously, you can get text pointers only after updating a BLOB to NULL.
   For example, the following code does not work in SQL Server 7.0, but works in SQL Server 2000.

   SET TRANSACTION ISOLATION LEVEL READ COMMITTED
   GO
   INSERT INTO t1 VALUES (4, NULL)
   BEGIN TRAN
   DECLARE @ptrval VARBINARY(16)
   SELECT @ptrval = TEXTPTR(c2)
   FROM t1
   WHERE c1 = 4
   WRITETEXT t1.c2 @ptrval 'x4'
   COMMIT
   

   In SQL Server 7.0, you must do the following:

   INSERT INTO t1 VALUES (4, NULL)
   UPDATE t1 
      SET c2 = NULL 
      WHERE c1 = 4
   DECLARE @ptrval VARBINARY(16)
   SELECT @ptrval = TEXTPTR(c2)
   FROM t1
   WHERE c1 = 4
   WRITETEXT t1.c2 @ptrval 'x4'
   

This table summarizes the differences.

Difference	in row text pointer	Non in row text pointer
Number	Maximum of 1024 active per transaction per database.	Unlimited.
Locking	Data row is S locked until the pointer becomes invalid. Locks are not obtained when the transaction is 'read uncommitted', or the database is 'single-user' or 'read-only' mode.	Data row is not locked.
Duration	Becomes invalid at the end of transaction or session, when a row is deleted or the schema of the table is changed.	Becomes invalid when the row is deleted.
NULL text	Obtainable right after the insert of NULL text.	Obtainable only after update.

Using ntext, text, and image Data with Database APIs

This is a summary of the ways the database APIs handle ntext, text, and image data:

• ADO
  ADO can map ntext, text, or image columns or parameters to a Field or Parameter object. Use the GetChunk method to retrieve the data one block at a time and the AppendChunk method to write data one block at a time.

• OLE DB
  OLE DB uses the ISequentialStream interface to support ntext, text, and image data types. The ISequentialStream::Read method reads the long data one block at a time, and ISequentialStream::Write writes the long data to the database one block at a time. For more information, see BLOBs and OLE Objects

• ODBC
  ODBC has a feature called "data-at-execution" to deal with the ODBC data types for long data: SQL_WLONGVARCHAR (ntext), SQL_LONGVARCHAR (text), and SQL_LONGVARBINARY (image). These data types are bound to a program variable. SQLGetData is then called to retrieve the long data one block at a time, and SQLPutData is called to send long data one block at a time. For more information, see Managing Text and Image Columns.

• DB-Library
  DB-Library applications also bind ntext, text, and image columns to program variables. The DB-Library function dbtxtptr is used to get a pointer to the location of the long column occurrence in the database. dbreadtext is used to read the long data one block at a time. Functions such as dbwritetext, dbupdatetext, and dbmoretext are used to write the long data one block at a time.

  Note

  Accessing in row text with DB-Library is not supported.

For more information, see Text and Image Functions (Transact-SQL).

See Also

Other Resources

DATALENGTH (Transact-SQL)
Data Types (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance