Permits minimally logged, interactive updating of an existing text or image column.
writetext [[database.]owner.]table_name.column_name text_pointer [readpast] [with log] data
is the name of the table and text or image column to update. Specify the database name if the table is in another database, and specify the owner’s name if more than one table of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.
a varbinary(16) value that stores the pointer to the text or image data. Use the textptr function to determine this value, as shown in example 1. text and image data is not stored in the same set of linked pages as other table columns. It is stored in a separate set of linked pages. A pointer to the actual location is stored with the data; textptr returns this pointer.
specifies that the command should modify only unlocked rows. If the writetext command finds locked rows, it skips them, rather than waiting for the locks to be released.
logs the inserted text or image data. The use of this option aids media recovery, but logging large blocks of data quickly increases the size of the transaction log, so make sure that the transaction log resides on a separate database device. See create database, sp_logdevice, and the System Administration Guide for details.
is the data to write into the text or image column. text data must be enclosed in quotes. image data must be preceded by “0x”. Check the information about the client software you are using to determine the maximum length of text or image data that can be accommodated by the client.
This example puts the text pointer into the local variable @val. Then, writetext places the text string “hello world” into the text field pointed to by @val:
declare @val varbinary(16) select @val = textptr(copy) from blurbs where au_id = "409-56-7008" writetext blurbs.copy @val with log "hello world"
declare @val varbinary(16) select @val = textptr(copy) from blurbs readpast where au_id = "409-56-7008" writetext blurbs.copy @val readpast with log "hello world"
The maximum length of text that can be inserted interactively with writetext is approximately 120K bytes for text and image data.
By default, writetext is a minimally logged operation; only page allocations and deallocations are logged, but the text or image data is not logged when it is written into the database. To use writetext in its default, minimally logged state, a System Administrator must use sp_dboption to set select into/bulkcopy/pllsort to true.
writetext updates text data in an existing row. The update completely replaces all of the existing text.
writetext operations are not caught by an insert or update trigger.
writetext requires a valid text pointer to the text or image column. For a valid text pointer to exist, a text column must contain either actual data or a null value that has been explicitly entered with update.
Given the table textnull with columns textid and x, where x is a text column that permits nulls, this update sets all the text values to NULL and assigns a valid text pointer in the text column:
update textnull set x = null
No text pointer results from an insert of an explicit null:
insert textnull values (2,null)
And, no text pointer results from an insert of an implicit null:
insert textnull (textid) values (2)
insert and update on text columns are logged operations.
You cannot use writetext on text and image columns in views.
If you attempt to use writetext on text values after changing to a multibyte character set, and you have not run dbcc fix_text, the command fails, and an error message is generated, instructing you to run dbcc fix_text on the table.
writetext in its default, non-logged mode runs more slowly while a dump database is taking place.
The Client-Library functions dbwritetext and dbmoretext are faster and use less dynamic memory than writetext. These functions can insert up to 2GB of text data.
The readpast option applies only to data-only-locked tables. readpast is ignored if it is specified for an allpages-locked table.
If the session-wide isolation level is 3, the readpast option is silently ignored.
If the transaction isolation level for a session is 0, writetext commands using readpast do not issue warning messages. These commands at session isolation level 0 modify the specified text column if the text column is not locked with incompatible locks.
SQL92 – Compliance level: Transact-SQL extension.
writetext permission defaults to the table owner, who can transfer it to other users.
Commands – readtext
Datatypes – text and image datatypes
