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!

 


Advertisements
Comments
  1. Satyabodhi says:

    Thanks for this post. Although the .WRITE pragma has been in T-SQL since 2005, this was the first time I noticed it.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s