SQL WRITE

What is SQL WRITE in SQL Server?

The SQL Server .WRITE clause updates a substring of a varchar(max), nvarchar(max), or varbinary(max) column within an UPDATE statement.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL WRITE: Supported: SQL Server 2005+ including Azure SQL Database. Not supported: PostgreSQL, MySQL, Oracle, SQLite, Snowflake, etc.

SQL WRITE Full Explanation

In SQL Server, the .WRITE clause is used inside an UPDATE statement to modify only a specific portion of a large value data-type column (varchar(max), nvarchar(max), varbinary(max)). Instead of rewriting the entire value, .WRITE lets you insert, overwrite, or truncate bytes/characters at a given position, improving performance and reducing logging for very large objects. If offset and length are NULL, the expression is appended to the existing value. If expression is NULL, the specified segment is deleted. .WRITE cannot target columns of other data types and must be used with SET. It works on tables, views (with updatable columns), and indexed views when allowed. The operation is fully logged unless the database is in bulk-logged mode, and it is compatible with triggers and constraints like any UPDATE.

SQL WRITE Syntax

UPDATE <schema.table>
SET <large_value_column>.WRITE (expression, @Offset, @Length)
WHERE <search_condition>;

SQL WRITE Parameters

expression - any varchar, nvarchar, or varbinary value to write (can be NULL)
@Offset - bigint; zero-based starting position. NULL appends expression.
@Length - bigint; number of bytes/characters to overwrite. NULL appends expression.

Example Queries Using SQL WRITE

-- 1. Overwrite 5 bytes starting at byte 10
UPDATE dbo.Documents
SET Data.WRITE (0xABCD, 10, 5)
WHERE Id = 42;

-- 2. Append text to an nvarchar(max) column
UPDATE dbo.Posts
SET Body.WRITE (N' Additional notes.', NULL, NULL)
WHERE PostId = 7;

-- 3. Delete first 1000 characters
UPDATE dbo.Logs
SET Message.WRITE (NULL, 0, 1000)
WHERE LogId = 555;

Expected Output Using SQL WRITE

Only the targeted segment of the column changes
Other columns and untouched parts of the large value remain intact
The statement returns the number of affected rows

Use Cases with SQL WRITE

Append data to a large blob without reallocating the entire value
Overwrite corrupted bytes in a file stored in varbinary(max)
Delete a chunk of text from an audit log entry
Stream partial updates from an app that uploads files in chunks

Common Mistakes with SQL WRITE

Using .WRITE on non-max columns – it only works with varchar(max), nvarchar(max), varbinary(max)
Supplying negative offset or length values
Forgetting zero-based indexing (first byte/char is 0)
Expecting automatic conversion between character and binary data
Assuming .WRITE is available in other database systems

First Introduced In

SQL Server 2005

Frequently Asked Questions

What data types does .WRITE support?

It only works with varchar(max), nvarchar(max), and varbinary(max) columns.

How do I append data to the end of a column?

Use NULL for both offset and length: column.WRITE(expression, NULL, NULL).

Can I use .WRITE inside a trigger?

Yes. .WRITE behaves like any other UPDATE and is fully compatible with triggers.

Does .WRITE reduce transaction log usage?

Yes. It logs only the changed bytes, making it more efficient than updating the entire value.