WRITETEXT - I'm utterly confused By BOL

From: Frank Thomas (No_at_Spam.org)
Date: 07/17/04 

Date: Sat, 17 Jul 2004 12:39:53 -0700

I just want to avoid the possibility of creating an application that works
most of the time, but periodically chokes because the planets aligned in a
way that resulted in an invalid text pointer.

Specifically, my objective is simply to enable users of an application to
store large strings of text in a SS2K database. By "large" I mean more text
than VarChar allows (will typically range from a few hundred bytes to 1MB).
I don't want to split the data amongst multiple VarChar columns - which
leaves me with Text or NText (AFAIK). Per business logic, any/all updates to
the text will entail a wholesale replacement of any/all text that exists
prior to the update. That is, these updates will not require updating a
subset of the text - the whole thing gets replaced. It will also be possible
for users of the application to remove all text for a given row (i.e., the
column for the row would contain a null value). In my research I found
WRITETEXT. Per BOL, this looks like what I need to accomplish what I want.

Rather than taking the development approach of "let's throw it on the wall
and see if it sticks" - I'm trying to understand what WriteText is all about
so that I implement the business requirements in a way that makes sense,
performs reliably and quickly, blah blah blah. But I'm confused by what BOL
describes...

Can someone explain this quote from BOL/SP3?

<BEGIN QUOTE>
For WRITETEXT to work properly, the column must already contain a valid text
pointer. {okay, I'm with you so far...}

If the table does not have in row text, SQL Server saves space by not
initializing text columns when explicit or implicit null values are placed
in text columns with INSERT, and no text pointer can be obtained for such
nulls. To initialize text columns to NULL, use the UPDATE statement. If the
table has in row text, there is no need to initialize the text column for
nulls and you can always get a text pointer.
<BEGIN QUOTE>

Am I supposed to initialize text columns to NULL or not? I know about null
values - but never heard of explicit null vs implicit null... Theory aside,
I have a system to build and deliver. What should I do given the
requirements as described above - should I create a default value of null
for my Text or NText column? Would that take care of this 'valid pointer'
issue? What value should I store in the column when the user decides to
remove all text content for a given row? If I receive a zero-length string
from the application, should I insert a null into the Text column?

Thanks!

Relevant Pages

  • Re: bug in CEPC boot loader!!!!
    ... Michel Verhagen (eMVP) wrote: ... and DOS needs a BIOS. ... without problems, or just initialize the most important devices (SDRAM, ... BIOSLOADER or LoadCEPC that is initializing the pointer at the location ...
    (microsoft.public.windowsce.platbuilder)
  • Re: Multiple indirection mess-up...
    ... >In the following program I allocate a block of pointers to type char, ... I suspect the fault is in the pointer manipulations between ... calloc sets each byte to all bits zero. ... Since you do properly initialize each pointer ...
    (comp.lang.c)
  • Re: FirstLook.com pop-up application; installed via SP?
    ... Better then batting in a fools game. ... > I didn't say it was a quote "from" you as you seem to have read it. ... >>> Microsoft supporter, but agree this is criminal behavior and whomever ... A pointer to that thread again: ...
    (microsoft.public.win2000.security)
  • Re: Question about arrays of derived types and POINTERs
    ... initialize surfinstead of allocating a temporary surfptr and then ... a pointer starts "life" with an undefined association ... nulifying the x, y, and z pointers in each element when you initialize ...
    (comp.lang.fortran)
  • Re: WRITETEXT - Im utterly confused By BOL
    ... No - a default constraint of NULL will not initialize the text pointer. ... MyId int NOT NULL ... MyTextData text NULL DEFAULT NULL ...
    (microsoft.public.sqlserver.programming)