A CTE is nothing but a query. It doesn't have a positive or negative impact any more than any other type of derived table will. Despite the fact that the name includes the word Table, nothing is stored in a CTE. Using a CTE within a query is the same as using a sub-select. It's a query. The only exception to this is when using a CTE recursively. Recursive queries are, by their nature, somewhat problematic. Running them against very large data sets if you don't have good indexing and the query taking advantage of that indexing could be problematic. But the issue here is the recursion, not the CTE itself.