Why Use Common Table Expressions

Common table expressions are beneficial to use when writing or managing complex queries. Being condensed or simplified, CTEs increase accessibility and digestion. Watch Steve’s video for more on CTEs……

Transcription of this video:

Why would you use common table expressions? I mean, you may have been writing SQL queries for 10 or more years, and have never used a common table expression to be able to do everything you need to do.

Well, one way is to simplify your query or test one part at a time. And if you ever looked at a query that was eight pages long in your editor with a six point font, well, those kinds of queries are really hard to follow. And oftentimes, when they’ve got lots and lots of sub queries in them, they can be broken up and done differently done as a common table expression where different parts of that query are extracted and named as the, the sort of that reusable CTE.

Another reason to use common table expressions is for recursion, and what recursion is, in computer science, it means when a function calls itself but in SQL Server, we look at recursion with CTE is when a query calls itself. That’s interesting, we can do some really interesting things there. If a query can call back and call itself recursive. Many many levels there to be able to do some interesting math and concepts with it. We can also make derived table queries much more readable by breaking them up into CTE is easier to test easier to read, easier to use. And it can be in certain cases an alternative to a temp table or a table variable. But don’t think of CTE is as a replacement for all cases of temp tables or table variables.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!