SQL SERVER – 7 Questions about OUTPUT Clause Answered

Just earlier I had posted two videos about the output clause and they both got an amazing response from all of you. Here you can watch both the videos 1) Insert Multiple Values into Multiple Tables in a Single Statement – SQL in Sixty Seconds #132 2) Remove Trigger for Delete – SQL in Sixty Seconds #133. After watching the videos, I got many questions from the users and I have listed them in today’s blog post. Let us discuss 7 Questions about the OUTPUT Clause Answered.

SQL SERVER - 7 Questions about OUTPUT Clause Answered outputclause-800x418

Question 1: Does the OUTPUT clause work with UPDATE statements like the INSERT and DELETE command showed in the video.
Answer 1: Yes, it does work with UPDATE. Additionally, it also works with the MERGE statement.

Question 2: Can I insert my data into a variable instead of the table?
Answer 2: Yes, you can insert your database into table variables instead of the real table. However, you must define your table variable in the same session as to where you have used the OUTPUT variable. Also if you are going to use table variable remember you will have to create this before you use the OUTPUT clause.

Question 3: Is it required to specify the column names of the target table?
Answer 3: It is always a good idea to specify the column names to insert the data and if you are not going to specify the column names, your table variable should have the same number of columns as the OUTPUT list (besides the identity and computed columns).

Question 4: Can I have a trigger on the table where I am inserting the data with the help of the OUTPUT clause?
Answer 4: No you can’t have a trigger on the table in which you are inserting the data.

Question 5: Can I use the OUTPUT clause for a single row or multiple rows?
Answer 5: Of course you can use it to process multiple rows. If you delete or insert multiple rows the variable DELETED or INSERTED will contain multiple rows.

Question 6: Can I use the DELETED keyword while I only INSERT the data?
Answer 6: No, you can’t. Similarly, you can’t use the INSERTED keyword when you only DELETE the data.

Question 7: What is the performance impact of using the OUTPUT clause?
Answer 7: While, I have overall seen the OUTPUT clause performing better than triggers, one should remember that in most cases the OUTPUT clause will return results to the client in the serial plan.

Here is another bonus question, let me know if you know the answer to this question. If yes, you can leave a comment.

Bonus Question: When was the OUTPUT clause introduced in SQL Server?

Leave a comment with the answer. Trust me you will be surprised.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Author: admin

1 thought on “SQL SERVER – 7 Questions about OUTPUT Clause Answered

  1. Do you mind if I quote a few of your articles as long as I provide credit and sources back
    to your website? My blog site is in the exact same area of interest as yours
    and my visitors would truly benefit from some of the information you provide here.
    Please let me know if this alright with you.

    Thank you!

Leave a Reply

Your email address will not be published.