Home > SQL Server 2005, SQL Tips > UPDATE statement with new .WRITE Clause

UPDATE statement with new .WRITE Clause


As per MS BOL the new .WRITE clause of the UPDATE DML statement enables partial or full updates and high performance appends to varchar(max), nvarchar(max) and varbinary(max) data type columns.

The UPDATE statement is logged; however, partial updates to large value data types using the .WRITE clause are minimally logged.

Syntax: .WRITE ( expression, @Offset , @Length )

Usage: The string specified in the expression param is replaced by the number of characters specified in
@Length param starting from the position mentioned in @Offset param.

Let’s check this with an example mentioned below: The “Senior” character set is replaced by the 3 length character “Sr.” starting from the 18th character.

-- Create a table containing a VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX) column:
CREATE TABLE CheckWrite (empID INT, eName VARCHAR(50), descr VARCHAR(MAX))

-- Insert test data in the table:
INSERT INTO CheckWrite
SELECT 101, 'Manoj Pandey', 'Manoj Pandey is a Sr. SQL Server developer and CRM analyst. He likes Photography & travelling.'

-- Check inserted record before UPDATE:
SELECT * FROM CheckWrite

-- Now UPDATE the descr column by using .WRITE clause:
UPDATE CheckWrite
SET descr .WRITE('Senior', 18, 3)
WHERE empID = 101

-- Check the updated result:
SELECT * FROM CheckWrite

-- Final cleanup
DROP TABLE CheckWrite

Output:-

Note: The .WRITE clause cannot be used to update a NULL column or set the value of column_name to NULL.

 

For more on UPDATE’s new .WRITE clause check the MSDN link here.

About these ads
  1. October 4, 2011 at 7:33 am

    Looks like a crude way of doing string replace. I do not know full syntax of .write but would it be nicer to do as following:

    UPDATE CheckWrite
    SET descr .WRITE(‘Sr.’, ‘Senior’)
    WHERE empID = 101

    Go ahead and replace every Sr. with Senior.

  2. October 4, 2011 at 7:54 am

    Thanks for your comments Sir!

    For every occurrence we have replace(), like this:
    UPDATE CheckWrite
    SET descr = replace(descr, ‘Sr.’, ‘Senior’)
    WHERE empID = 101

    STUFF() is also there to replace at specific positions. But this is more performant than other approaches as it is a minimally logged operation and will work on [N]VARxxxx(MAX) type columns.

  3. October 4, 2011 at 8:17 am

    I didn’t knew there was a .WRITE!

    • October 4, 2011 at 8:22 am

      Same here, today only I just stumbled on this new feature added in SQL Server 2005.

  4. dennis
    November 11, 2011 at 9:02 am

    can’t you always use table-variables in your examples?

    “CREATE TABLE CheckWrite (empID INT, eName VARCHAR(50), descr VARCHAR(MAX))”

    declare @CheckWrite TABLE (empID INT, eName VARCHAR(50), descr VARCHAR(MAX))

    that would remove the need for cleaning up the database after tryout :)

  1. November 19, 2012 at 12:37 am

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

Follow

Get every new post delivered to your Inbox.

Join 396 other followers

%d bloggers like this: