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

UPDATE statement with new .WRITE Clause – SQL Server


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.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 


  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 🙂

    • November 19, 2014 at 9:55 am

      Sure Dennis… thanks for the suggestion… will try to use table variables whenever necessary.

      Sorry for the late response 🙂

      ~Manoj

  5. Yuvraj
    March 29, 2017 at 8:08 pm

    You just saved me 🙂 Didn’t knew about this clause

  1. November 19, 2012 at 12:37 am
  2. March 15, 2015 at 3:42 am
  3. December 5, 2020 at 8:17 am
  4. January 7, 2021 at 5:00 am
  5. January 6, 2022 at 5:10 am

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.