writetext

Permits minimally logged, interactive updating of an existing text, unitext or image column.

Syntax

writetext [[database.]owner.]table_name.column_name
	text_pointer [readpast] [with log] data

Parameters

Examples

Usage

  • The maximum length of text that can be inserted interactively with writetext is approximately 120K bytes for text, unitext or image data.

  • By default, writetext is a minimally logged operation; only page allocations and deallocations are logged, but the text, unitext 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 logs operations on in-row LOB columns.
  • You can run the writetext command (with or without the with log parameter) simultaneously with the online parameter.
  • 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 and for this reason, updating with writetext does not result in an update of the row timestamp.

  • writetext requires a valid text pointer to the text, unitext or image column. For a valid text pointer to exist, a text, or unitext 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.

See also Converting text and image Datatypes in Reference Manual: Building Blocks.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

You must be the table owner or a user with update permission to run writetext.

Related reference
readtext