CTE vs. Table Variable

Question

CTE vs. Table Variable

g33kspeak

Mr or Mrs. 500

Points: 531
More actions
September 30, 2010 at 12:02 pm

#241202

I'm not new to SQL coding, although I was not aware of the CTE function until this week.
I was looking over this article: http://www.sqlservercentral.com/articles/T-SQL/62159/
It occurred to me, that the CTE is being used much like a Table Variable or Temp Table in this particular example.
It is populated in the same way, using a subset of data from other locations, formatted a certain way and then joined into itself to give the results needed.
What is the benefit to using a CTE vs. a Table Variable or Temp Table to achieve the same results?
Better execution?
Thanks! 🙂

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Nevyn SSChampion Points: 14210 More actions · Answer 1

A CTE is more like a temporary view or a derived table than a temp table or table variable.

They are used for very different things. Temp tables are temporary STORAGE, you move data to them, and then can perform multiple queries/operations on that data. A CTE is to be used only with a single query, and you arent copying data to it. They are, for the most part, a more readable way of writing a query you could write anyway.

g33kspeak Mr or Mrs. 500 Points: 531 More actions · Answer 2

Thanks Nevyn, I'll keep researching until I understand them better.

Nevyn SSChampion Points: 14210 More actions · Answer 3

g33kspeak (9/30/2010)
Thanks Nevyn, I'll keep researching until I understand them better.

They threw me off at first, too.

This might help clarify[/url]

g33kspeak Mr or Mrs. 500 Points: 531 More actions · Answer 4

g33kspeak

Mr or Mrs. 500

Points: 531

September 30, 2010 at 12:41 pm

#1229825

Thanks for the link!

WayneS SSC Guru Points: 95460 More actions · Answer 5

FWIW, I tend to think of a CTE as a pre-defined sub-query. (Of course, a recursive CTE is slightly different.)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Mohan Paribrajak SSC Veteran Points: 230 More actions · Answer 6

One of the usage of CTE is to determine the hierarchy where the depth is not known.

It allows you to traverse UP or DOWN in a recursive manner.

g33kspeak Mr or Mrs. 500 Points: 531 More actions · Answer 7

One question I have....

From what I was reading, you define the CTE, then you can query against, it, update, etc.

But it is a one time query, right? Something like this works:

;With CTE (TestID, TestFld) AS

(Select TestID, TestFld From TestTbl Where TestFld like 'xxx%')

Select * from CTE

But not something like this:

;With CTE (TestID, TestFld) AS

(Select TestID, TestFld From TestTbl Where TestFld like 'xxx%')

Select * from CTE

Select * from CTE where TESTID Between 1 and 10

Am I right?

Nevyn SSChampion Points: 14210 More actions · Answer 8

That's correct.

On the other hand, you can define multiple CTE's for use with one query.

g33kspeak Mr or Mrs. 500 Points: 531 More actions · Answer 9

I saw that, and the only other question I think I have right now, is that you have to do the query referencing the CTE immediately after the declaration, correct?

WayneS SSC Guru Points: 95460 More actions · Answer 10

Yes. Note that you can use a CTE with an INSERT, UPDATE or DELETE statement in addition to the SELECT statement.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 11

You can reference the same CTE multiple times in your later queries. This is a nice feature. But be aware that SQL seems to re-run the CTE rather than "cache" and "reuse".

So, check the plan carefully when you re-reference a CTE. It's likely SQL is re-running the query for every reference to it.

Scott Pletcher, SQL Server MVP 2008-2010