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.
The APPLY operator can be a bit confusing, but if you master how it works, you can do great things with it. It is similar to a JOIN (hence the confusion), but differs in one key area: The JOIN defines a relation between two datasets using the ON keyword, whereas APPLY does not.
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.
Indexed views are views where the computed view data is already calculated and stored in the database, which make them super-fast for querying. And if you’re using the expensive Enterprise Edition of SQL Server, you’ll enjoy performance increases in other queries that can make use of the indexed view as well.