CTE vs. Table Variable
-
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!
-
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.
-
Thanks Nevyn, I'll keep researching until I understand them better.
-
Thanks for the link!
-
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 -
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.
-
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 CTEBut not something like this:
;With CTE (TestID, TestFld) AS(Select TestID, TestFld From TestTbl Where TestFld like 'xxx%')Select * from CTESelect * from CTE where TESTID Between 1 and 10Am I right?
-
That's correct.
On the other hand, you can define multiple CTE's for use with one query.
-
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?
-
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 -
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
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply