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.

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)