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.

Book Review - SQL Server Transaction Log Management

I was recently sent a review copy of "SQL Server Transaction Log Management" by Tony Davis and Gail Shaw, a new book from Red Gate (who publish the SimpleTalk series of websites and books.


This book is part of the Stairway series of books, and as such tackles a narrow subject, but from the very basics to an expert level. I found that the process of building from simple basics to a more in depth discussion of the technical details was accessible both to those who might have inherited a system and need to know how to properly configure it to minimise risk, and those who have a more detailed understanding.

Whilst this book does tackle data recovery, it is more about prevention and putting yourself in the position of avoiding the disaster in the first place. The process used in this book allows you to take a look at the reasons why you would choose to manage your transaction log in various ways, what implications this might have, and how that might impact upon your service level agreements (time and amount of potential data loss in a disaster) to the business. It deals with why and how to back up the transaction log in order to minimise data loss, and the implications of a corruption or loss of a log backup.

The book has detailed chapters on Full and Bulk Logged recovery modes, and even deals with Simple mode, and the ways in which the transaction log is used in each of them. It also goes through common scenarios (run away growth of a transaction log, disaster recovery, switching modes) to examine the implications and ways forward for each. It also looks at how to optimise your log so that you get the best performance for your intended use of the system, and how to monitor the log to check it is working optimally.

The style of the book is very much that of a taught example, and as such it allows the reasons why a course of action is desirable (or not) to be reinforced with a worked example, and specifies where decisions have been taken that aren't ideal for a production environment.

This is the sort of book I would give to a Junior DBA, to familiarise him or her with why transaction logs are configured as they are, and will keep for my own shelf to remind myself of the more technical details as to what is going on inside SQL Server in various log operations.