varchar(max), which holds non-Unicode variable length data.
nvarchar(max), which holds Unicode variable length data.
varbinary(max), which holds variable length binary data.
These data types can store up to 2^31-1 bytes of data.
For updating large value data types the UPDATE command now includes the .WRITE method:
UPDATE <table_or_view_name>
SET column_name = .WRITE ( expression , @Offset , @Length )
FROM <table_source>
WHERE <search_condition>
The parameters of the .WRITE method are described in following table.
Argument Description
expression The expression defines the chunk of text to be placed in the column.
@Offset @Offset determines the starting position in the existing data the new text should be placed. If @Offset is NULL, it means the new expression will be appended to the end of the column (also ignoring the second @Length parameter).
@Length @Length determines the length of the section to overlay.
Referenced from:
SQL Server 2005 T-SQL Recipes A Problem-Solution Approach