Performance tuning the other day, I was stumped by a query plan I was looking at. Even though I had constructed a covering index, I was still getting a Key Lookup operator in my query plan. What I usually do when that happens is to check the operator’s properties to see what its output columns are, so I can include those columns in my covering index.
Here’s the interesting thing: there weren’t any output columns. What happened?
The MERGE statement is a very powerful way to combine INSERT, UPDATE and/or DELETE in a single statement. But there’s more than meets the eye. There are situations where you could use a MERGE statement to perform just one of those tasks.
The MERGE statement is a really powerful way to create what’s called “upserts”. In this article, I’ll take a more detailed look at how you can make the best use of MERGE and I’ll also show you some cool tricks I picked up along the way.
The OUTPUT clause allows you to combine DML statements with a kind of SELECT statement on the rows affected by the DML operation. This is a powerful way to visualize what records were touched by your statement, or an easy way to build an auditing mechanism.