SQL with Manoj

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.