I’m going to begin by saying that you should always use set based operations instead of loops when set based operators are an option. If you don’t understand that please read this blog from Microsoft’s SQL team. Set operations have three significant benefits:
- In T-SQL set operations will outperform loops, usually by several orders of magnitude (meaning they are hundreds or thousands of times faster, hours to seconds improvements)
- Set operations usually involve less code, so they have fewer defects.
- Set operations usually involve less code, so they are quicker to code.
Still, there are lots of things that you can not do in a set operation.
Read the rest on the Optimal BI blog.