Inserting words in varchars

It recently occurred to me that not all SQL developers are aware of all of the ways to update string data in SQL server.

Let's consider a simple table:

CREATE TABLE TempStuff
    (
      i INT IDENTITY(1, 1)
            PRIMARY KEY ,
      mytext VARCHAR(MAX) ,
      myshorttext VARCHAR(1000)
    )

Let's put some data in it:

INSERT dbo.TempStuff
        ( mytext, myshorttext )
VALUES  ( 'This is text', -- mytext - varchar(max)
          'This is text'  -- myshorttext - varchar(1000)
          )
SELECT * FROM dbo.TempStuff

So, let's say you want to insert a word into our text. There are several ways of doing it:

The "left-write-right" method:

UPDATE  dbo.TempStuffSET     mytext = LEFT(mytext, 7) + ' test' + RIGHT(mytext, LEN(mytext) - 7) ,
        myshorttext = LEFT(myshorttext, 7) + ' test' 
                + RIGHT(myshorttext, LEN(myshorttext) - 7)

SELECT  * FROM    dbo.TempStuff

This has simplicity, and is easy to understand for the new coder, but is not the only method. You can use the STUFF function:

UPDATE  dbo.TempStuffSET     mytext = STUFF(mytext, 9, 0, 'stuffed ') ,
        myshorttext = STUFF(myshorttext, 9, 0, 'stuffed ')


SELECT  * FROM    dbo.TempStuff

However, I recently found a method I had been previously unaware of, specifically for the varchar(max), nvarchar(max), varbinary(max) types (it doesn't work on, for example, other varchar fields as they are not treated the same way within SQL server). This is the .WRITE method, used to put some text at the end of a string:

UPDATE  dbo.TempStuff
SET     mytext.WRITE(' written at the end',NULL,0)

SELECT * FROM dbo.TempStuff

The syntax is:

StringToUpdate.WRITE(Newtext,StartPosition,CharactersToDelete)

(if StartPosition is null, the end of the string to be updated is used)

Now, given the alternatives and limitations, why would you use .WRITE? Well, whereas other methods need to read the text, update it, and write it, this only needs change the updated data. This is important where you have large data, as putting text on the end of the column is minimally logged (if you are in bulk-logged or simple modes) which can be faster. This MSDN (see the section "Updating Large Value Data Types") notes that it is most effective if 8060 bytes are inserted at a time.