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

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.