A CTE is a Common Table Expression in T-SQL. It was introduced in SQL Server 2005. This T-SQL construct can be used to gather information for your query much like a sub-select or a view.
Many developers ask me when to use a CTE and when to use some other method and my answer is…it depends…because it really does. My own personal opinion on CTEs is that they were included in T-SQL to enable recursive querying of tables, and it does that very well. Because of that, however, the performance of a CTE tends to be not as efficient as other constructs. So my general guideline is: only use a CTE when you need to. When I am given a piece of code to performance tune and it contains a CTE I usually look at the plan right away and decide if it might be worth re-writing. If so I will re-write the query using some other technique (sub-selects, temp tables for instance) and check the plan for the re-write to see if it is more efficient.
Microsoft says that a CTE can be used to:
- Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
- Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
- Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
- Reference the resulting table multiple times in the same statement.
So, to state my guideline another way, use a CTE when it fits the definition above, and not just for code readability.
Generally speaking, I find that the plans created for CTEs tend to be more of a “looping” type of access to the data, whereas using sub-selects, joins, or temp tables tend to be more of a set-based join type of access. I look at the number of executions in the plan for different operations. When I see that the number of executions is greater than one (and usually is the same as the estimated row count) I know that this “looping” is occurring. This is a good thing for recursive CTEs, not so good for regular CTEs. My observations are based on SQL Server 2008 R2, so this may change in future releases.
There are lots of arguments for and against CTEs out there, and all may be valid based on each author’s experience. My point is basically one of performance; I see lots of articles out there that tell me to remove a CTE if I am having a performance problem, but I don’t see articles that tell me to add a CTE if I am having a performance problem.
I’m not going to repeat what others have said about CTEs at this point; here are some links that I think summarize the use of CTEs well. Please refer to them for more details.
CTEs in SQL Server 2005 – a good summary to start:
CTEs vs. Temp Tables:
A longer read, but with some good performance examples: