asked
What are the implications of using CTE on large datasets
I know the basic functionality of CTE but would want to know if it is good to use on large data sets and what problems might it cause.
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.
17 People are following this question.
