As of SQL Server 2008, there’s a new powerful consolidation statement in the DML toolbox: MERGE. Using MERGE, you can perform so-called “upserts”, i.e. one statement that performs an insert, delete and/or update in a single statement. And, more importantly, with just a single join.
Remember all the times you’ve written long loop statements? Often, a recursive common table expression can be a much better solution, not to mention a lot more pleasing to the eye. The recursive CTE builds on the same basic framework as a regular CTE, except it consists of two parts: the anchor and the recursion.
What’s a common table expression?
Common table expressions are a great way to clean up your code and make it more readable. A CTE resembles a subquery, but unlike subqueries, you can re-use the same data set over and over again in your code. Sometimes, you’ll even find that the query optimizer does a better job when you use a common table expression, resulting in a faster-running, more efficient query.