Some of you might already know about the .WRITE clause in the UPDATE statement, but I just learnt about it the other day and would therefore like to share about it for the benefit of all.

Almost every system has a couple of system default values that get populated each time a new database for the product is created/initialized. The system that I work on is no different. In a particular release, we needed to reword some of the terminologies in our product. For the purposes of this post, let’s assume that we needed to update the usage of “SQL Server” to read “Microsoft SQL Server”.

The Scenario

To keep things simple, allow me to create a table with a single record.

USE tempdb
GO
--Safety Check
IF OBJECT_ID('UpdateWRITETest') IS NOT NULL
    DROP TABLE dbo.UpdateWRITETest
GO

--Create test table
CREATE TABLE dbo.UpdateWRITETest (ProductDescription VARCHAR(MAX))
GO

--Insert test data
INSERT INTO dbo.UpdateWRITETest (ProductDescription)
VALUES ('SQL Server has a long history as the relational database solution from Microsoft.'),
       ('Find information about previous versions of SQL Server, including product details, technical resources, and support information.');
GO

Normally, one would have used one of the following mechanisms (there are many possible variants, based on individual preference) for the updates to default data.

The Conventional Solutions

The possible conventional solutions involve the use of REPLACE and the STUFF functions.

USE tempdb
GO
--Method 01
UPDATE upd
SET upd.ProductDescription = REPLACE(ProductDescription,'SQL Server','Microsoft SQL Server')
FROM dbo.UpdateWRITETest AS upd
GO

--Method 02
UPDATE upd
SET upd.ProductDescription = STUFF(ProductDescription,CHARINDEX('SQL Server',ProductDescription,1),LEN('SQL Server'),'Microsoft SQL Server')
FROM dbo.UpdateWRITETest AS upd
GO

The UPDATE statement – .WRITE clause

However, a close friend suggested I relook the Books On Line page of the UPDATE statement. Since his statements are often cryptic, I opened up the help for the UPDATE statement and found the .WRITE clause. Notice that I deduct 1 from the value returned by CHARINDEX (i.e. the position of the search string). This is because .WRITE uses a 0-based position index.

USE tempdb
GO
--UPDATE .WRITE method
UPDATE upd
SET ProductDescription .WRITE('Microsoft SQL Server',(CHARINDEX('SQL Server',ProductDescription,1)-1),LEN('SQL Server'))
FROM dbo.UpdateWRITETest AS upd
GO

Features:

While functionally similar to the more generic STUFF statement, the .WRITE clause is an integral part of the UPDATE statement, and has the following features:

• Works for VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX)
• .WRITE performs a partial or full update
  • For example, if only the first 200 characters of a large string value are to be updated, a partial update would be performed
  • This prevents delete and subsequent modification of all data in the column, which would happen in the case of REPLACE
• Exceptions to .WRITE being a partial update are taken when
  • Changes are made to key columns of a partitioned view or table
  • Modifies more than one row and also updates the key of a nonunique clustered index to a nonconstant value
• If the statement inserts or appends new data, the operation is minimally-logged if the database recovery model is simple or bulk-logged
  • Updates to existing data are not minimally-logged
• .WRITE cannot be used to update a NULL column or set the value of a column to NULL

Because the .WRITE clause performs a partial update, I am researching more into the performance aspects of the .WRITE statement. I will write a follow-up post once the research is complete.

Reference:

• UPDATE statement: http://msdn.microsoft.com/en-us/library/ms177523.aspx
• STUFF: http://msdn.microsoft.com/en-us/library/ms188043.aspx
• REPLACE: http://msdn.microsoft.com/en-us/library/ms186862.aspx

Until we meet next time,

Be courteous. Drive responsibly.