Use writetext to change text, unitext, or image values when you do not want to store long text values in the database transaction log. Generally, do not use the update command, because update commands are always logged. In its default mode, writetext commands are not logged.
To
use writetext in its default, non-logged state,
a system administrator must use sp_dboption to
set select into/bulkcopy/pllsort on.
This permits the insertion of non-logged data. After using writetext,
you must dump the database. You cannot use dump transaction after
making unlogged changes to the database.
The writetext command overwrites any data in the column it affects. The column must already contain a valid text pointer.
Use the textvalid() function to check for a valid pointer:
select textvalid("blurbs.copy", textptr(copy))
from blurbs
There are two ways to create a text pointer:
insert actual data into the text, unitext, or image column
update the column with data or a NULL
An “initialized” text column uses 2K of storage, even to store a couple of words. Adaptive Server saves space by not initializing text columns when explicit or implicit null values are placed in text columns with insert. The following code fragment inserts a value with a null text pointer, checks for the existence of a text pointer, and then updates the blurbs table. Explanatory comments are embedded in the text:
/* Insert a value with a text pointer. This could
** be done in a separate batch session. */
insert blurbs (au_id) values ("267-41-2394")
/* Check for a valid pointer in an existing row.
** Use textvalid in a conditional clause; if no
** valid text pointer exists, update ’copy’ to null
** to initialize the pointer. */
if (select textvalid("blurbs.copy", textptr(copy))
from blurbs
where au_id = "267-41-2394") = 0
begin
update blurbs
set copy = NULL
where au_id = "267-41-2394"
end
/*
** use writetext to insert the text into the
** column. The next statements put the text
** into the local variable @val, then writetext
** places the new text string into the row
** pointed to by @val. */
declare @val varbinary(16)
select @val = textptr(copy)
from blurbs
where au_id = "267-41-2394"
writetext blurbs.copy @val
"This book is a must for true data junkies."
For more information on batch files and the control-of-flow language used in this example, see Chapter 15, “Using Batches and Control-of-Flow Language.”
