Performace between CTE and Temporary table

Question

1.00/5 (1 vote)

See more:

SQL

server

Hi,

For which performace will be high. CTE or Temporary table in SQL server?

Kindly advise on this

Posted 7-Dec-15 20:12pm

Member 12192823

Add a Solution

Comments

Tomas Takac 8-Dec-15 4:18am

Well, it depends. There is no "one size fits all" answer. It's up to you to tune the query. Try several combinations of CTE and temp tables and compare the performance.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2015-12-11T13:15:00

They serve different purposes.
A CTE is stored in memory if it's materialized, and can only be used within one query.
The data in a temp table is persisted until it goes out of scope, and can therefore be used by many queries.
And a temp table can be indexed.

So the answer to your question would normally be that the CTE is at least as fast or faster, but the exception is when an indexed temp table makes a query so much faster that it compensates for the extra resources needed for persisting it.

CHill60 · Answer 2 · 2015-12-11T05:02:00

Solution 1

You really need to research this properly ... it's sort of off-topic for this forum.

There is a discussion over at sqlservercentral[^] on the subject which also includes links to other articles.

Posted 11-Dec-15 5:02am

CHill60