Benefits of using the OUTPUT clause in SQL statements for data modification

When multiple threads access the database concurrently, the OUTPUT statements can often help you solve some common but tricky problems.

Here is a simple example. A common scenario for accessing a database is to find a corresponding record based on certain conditions, and then Update it, then read the updated record content (sometimes you need to read it before inserting it into another table ).

For example, Declare @ Id int
Select Top 1 @ Id = ID From MyTable Where Status = 0

Update MyTable Status = 1 From MyTable Where Id = @ Id

(Insert into SecondTable) Select * From MyTable Where Id = @ Id

It can be simply merged into one sentence:

Update top (1) MyTable Set Status = 1
Output inserted .*
(INTO SecondTable)
FROM MyTable
Where Status = 0

The benefits of merging are not just as simple as executing only once. In multi-thread concurrent access, the first method requires users to be familiar with the concepts of locks, transactions, isolation levels, and so on. Otherwise, deadlocks and data conflicts may easily occur. Second, even at the SQL Server default Read Commited isolation level, it is difficult to have a big problem. You can even include multiple outputs in a sentence:

Update top (1) MyTable Set Status = 1
Output inserted. *, 1 INTO SecondTable
OUTPUT 1, 2, INSERTED. ID
FROM MyTable
Where Status = 0

It is very convenient. For more information about the OUTPUT usage, see SQL Server online series.

-- Note: This article is too small and too handy. Sorry, I am sorry to have read it. I just saw a Blog about concurrent access, so I wrote this section. The Blog is here: Database concurrency-beauty programmer