CODEWITHSUNDEEP
sqlsql-server
kuniklo
kuniklo

Reputation: 31

Is UPDATE containing .WRITE clause atomic when multiple columns are updated?

Is the following statement an atomic operation?

UPDATE [table1]
SET column1=@val1, column2.WRITE(@val2, NULL, 0) WHERE table1_id=@id

or should it be placed inside a transaction to ensure that none or both columns are updated in case of error situations?

Columns are defined as:

column1 int not null, 
column2 varbinary(max) not null

Upvotes: 3

Views: 742

Answers (2)

criticalfix
criticalfix

Reputation: 2870

Here is an MSDN reference on Autocommit Transactions, which states that:

Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back.

Modifying Large-Value (max) Data in ADO.NET using UPDATE .WRITE is not documented to affect the UPDATE statement transaction.

Upvotes: 1

tommy_o
tommy_o

Reputation: 3783

It is one statement. In SQL Server, each statement carries an implied transaction. Here is another answer if you'd like more details.

What does a transaction around a single statement do?

Upvotes: 2

Related Questions