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
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.
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.
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.
I didn’t knew there was a .WRITE!
Same here, today only I just stumbled on this new feature added in SQL Server 2005.
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 🙂
Sure Dennis… thanks for the suggestion… will try to use table variables whenever necessary.
Sorry for the late response 🙂
~Manoj
Also discussed in MSDN forum: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/77aefe15-6bac-4455-b440-95b85cff2ef9
Also discussed in MSDN forum: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d27d7ddf-7a5d-45b5-b2bc-3e8624fb5f7b
You just saved me 🙂 Didn’t knew about this clause