Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

SQL Server 2005 UPDATE statement with new .WRITE Clause

Oct 7 2011 8:37AM by Manoj   

As per MS BOL the new .WRITE clause in 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.

<pre class="brush: plain">
Syntax: .WRITE ( expression, @Offset , @Length )
</pre>

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 

    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
    

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

Read More..   [25 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Manoj
240 · 1% · 188
8
 
0
Knew
 
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

3  Comments  

  • This is a good tip to feature, but your "usage" description is inaccurate. Compare to this: Usage: The string specified in the expression param replaces 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 replaces the 3 length character "Sr.", starting from the 18th character.

    commented on Oct 7 2011 9:28AM
    david.blair@eagletm.com
    1348 · 0% · 17
  • Isn't this the same as STUFF i.e.

    Update CheckWrite SET descr = STUFF(descr,18,3,'Senior') where empid = 101

    or are there performance benefits from using .WRITE?

    commented on Oct 8 2011 11:57AM
    TheAndrew
    1925 · 0% · 10
  • Yes, this is similar to STUFF() function.

    But as I mentioned its particularly for VARCHAR(MAX), NVARCHAR(MAX) & VARBINARY(MAX) datatype columns with minimal logging, thus is fast.

    commented on Oct 10 2011 1:55AM
    Manoj
    240 · 1% · 188

Your Comment


Sign Up or Login to post a comment.

"SQL Server 2005 UPDATE statement with new .WRITE Clause" rated 5 out of 5 by 8 readers
SQL Server 2005 UPDATE statement with new .WRITE Clause , 5.0 out of 5 based on 8 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]