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”.
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
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.
- 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,
I’d probably emphasize that it works ***only*** for varchar(max), nvarchar(max), varbinary(max).
Recalling my older post: [http://sqlwithmanoj.wordpress.com/2011/10/04/update-statement-with-new-write-clause/]