WikiDBA

The .WRITE clause is an integral part of the UPDATE statement. Commonly it’s used to perform a partial update on big data set of VARCHAR (MAX), NVARCHAR (MAX) and VARBINARY (MAX) data types. Its functionally is very similar to the standard STUFF statement. The UPDATE statement is logged, however, partial updates to large value data types using the .WRITE are minimally logged.
In general practice we use REPLACE or STUFF function to update partial data of a big data values.

To demonstrate this , here I am creating a test table as :

IF OBJECT_ID(‘VirendraTest’) IS NOT NULL
DROP TABLE dbo.VirendraTest
GO

–Create a table as ‘VirendraTest’

CREATE TABLE dbo.VirendraTest (Details VARCHAR(MAX))|
GO

–Insert test data
INSERT INTO dbo.VirendraTest (Details)
VALUES (‘VIRENDRA YADUVANSHI – Microsoft SQL Server Database Architect | Consultant | Blogger | Specialist | DBA | Speaker’);
GO

— Check test data

Select * from VirendraTest

Now, let see syntax of WRITE

.WRITE ( expression, @Offset , @Length )

 As per BOL – The .WRITE (expression, @Offset, @Length) clause to perform a partial or full update of varchar(max), nvarchar(max), and varbinary(max) data types. For example, a partial update of a varchar(max) column might delete or modify only the first 200 characters of the column, whereas a full update would delete or modify all the data in the column. .WRITE updates that insert or append new data are minimally logged if the database recovery model is set to bulk-logged or simple.

Suppose, here I want to change word ‘Microsoft’ as ‘MS’, there may be 2 options, either use of REPLACE or STUFF as

–Option 1

UPDATE VT
SET VT.Details = REPLACE(Details,‘Microsoft’,‘MS’) FROM dbo.VirendraTest AS VT
GO

–Option 2
UPDATE VT
SET VT.Details =STUFF(Details,CHARINDEX(‘Microsoft’,Details,1),LEN(‘Microsoft’),‘MS’)
FROM dbo.VirendraTest AS VT
GO

 

Now same thing with .WRITE

–UPDATE with .WRITE option

UPDATE VT SET Details.WRITE(‘MS’,(CHARINDEX(‘Microsoft’,Details,1)-1),LEN(‘Microsoft’))
FROM dbo.VirendraTest AS VT
GO

 Please do comment on this performance tips

Happy Reading!