What are the advantages/disadvantages of using a CTE?

Question :

What are the advantages/disadvantages of using a CTE?,

Answer :

I’m looking at improving the performance of some SQL, currently CTEs are being used and referenced multiple times in the script. Would I get improvements using a table variable instead? (Can’t use a temporary table as the code is within functions).


You’ll really have to performance test – There is no Yes/No answer. As per Andy Living’s post above links to, a CTE is just shorthand for a query or subquery.

If you are calling it twice or more in the same function, you might get better performance if you fill a table variable and then join to/select from that. However, as table variables take up space somewhere, and don’t have indexes/statistics (With the exception of any declared primary key on the table variable) there’s no way of saying which will be faster.

Read More  Packaging Java apps for the Windows/Linux desktop

They both have costs and savings, and which is the best way depends on the data they pull in and what they do with it. I’ve been in your situation, and after testing for speed under various conditions – Some functions used CTEs, and others used table variables.

That’s the answer What are the advantages/disadvantages of using a CTE?, Hope this helps those looking for an answer. Then we suggest to do a search for the next question and find the answer only on our site.

Read More  Removing N items from a list conditionally

Disclaimer :

The answers provided above are only to be used to guide the learning process. The questions above are open-ended questions, meaning that many answers are not fixed as above. I hope this article can be useful, Thank you