What Exactly Is a CTE in T-SQL? A Comprehensive Guide with 7 Examples

Question

Post reply

What Exactly Is a CTE in T-SQL? A Comprehensive Guide with 7 Examples

Parthprajapati

SSC Veteran

Points: 252
More actions
October 20, 2023 at 12:00 am

#4305149

Comments posted to this topic are about the item What Exactly Is a CTE in T-SQL? A Comprehensive Guide with 7 Examples
October 23, 2023 at 8:34 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 16 total)

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

Mike01 SSChampion Points: 11395 More actions · Answer 1

can you provide a link to the ddl and data?

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

This is nice but I agree with Mike01. Where do we get a copy of the DDL for the table AND the data? People can learn much more by actually trying things out and they need both the table and the data to do that.

I'm actually a bit surprised that this article was allowed to be released without it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

YSLGuru SSC-Insane Points: 21661 More actions · Answer 3

Glad to see Jeff is here, I know he'll have some interesting insight onnthis question.

CTE or View, which to use and when as well as why?

I personally use both a lot but Im curious if there are any formalized rules on this question or is it often a personal preference matter? And last but not least, where does performance play a part in deciding on which to use?

Thanks

Kindest Regards,

Just say No to Facebook!

David Lafayette SSCommitted Points: 1559 More actions · Answer 4

Nice breakdown of CTEs. I find them extremely useful for logically building queries and maintainability.

One thing I think is important to remember about CTEs is that they are syntactical simplifications - that’s it. They do not materialize each table expression. With the exception of recursive CTEs, they are basically pre-defined sub-queries, still extremely useful, but they do not offer performance gains.

Referencing a table expression more than once will actually executes that expression (sub-query) multiple times.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

There's really no difference between CTEs and Views. The exception to that rule is that if you can force a "Blocking Operator" in the CTE, it can act more like a table than the "inline view" form that it normally takes because the "Blocking Operator" forces materialization behind the scenes (frequently as an "Eager Spool").

Of course, then there are these wonderful things called "Indexed Views" that have huge performance benefits when used as a form of "pre-aggregation" (thank you for the term, Peter Larsson!) in a CTE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

I'm still waiting on the OP for the DDL and Data sources for the example table.

As a bit of a sidebar, I've not yet done a deep dive on this article but I can tell you a bit about Example 3. While it IS an example of using multiple CTEs, I wouldn't use multiple CTEs to solve the given problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

YSLGuru SSC-Insane Points: 21661 More actions · Answer 7

This is why I mentioned Jeff.

Kindest Regards,

Just say No to Facebook!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

p.s. CTEs are inherently NOT reusable anywhere except the query that they live in. They can be referenced as many times as you need to with the understanding that they DO work like views and the entire CTE will be re-executed every time it's referenced.

Views don't have such a limit because they are a separate object. They, too, will be executed once each time they are referenced even in the same query.

CTE's are inline code and you don't have to go anywhere else to find the code for it. Views are separate objects and, if you want to see what's in the view for code, you'll need to open another window and go look at it separately from the code you're working on.

Views can also be constructed with CTEs and an outer query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

YSLGuru wrote:

Glad to see Jeff is here, I know he'll have some interesting insight onnthis question. CTE or View, which to use and when as well as why?

Thank you for the very kind words. I wish the author would show up with some data or at least a link to where the data is. It's been 4 days and not a peep. 🙁

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 10

Jeff Moden wrote:

p.s. CTEs are inherently NOT reusable anywhere except the query that they live in. They can be referenced as many times as you need to with the understanding that they DO work like views and the entire CTE will be re-executed every time it's referenced.
Views don't have such a limit because they are a separate object. They, too, will be executed once each time they are referenced even in the same query.
CTE's are inline code and you don't have to go anywhere else to find the code for it. Views are separate objects and, if you want to see what's in the view for code, you'll need to open another window and go look at it separately from the code you're working on.
Views can also be constructed with CTEs and an outer query.

This isn't quite correct - neither CTEs or Views are executed (materialized). Both will be incorporated into the outer query - and then the execution plan is generated.

Now, if you meant that the source tables are accessed each time a CTE/View is referenced - then that may be true depending on the usage and the execution plan that is generated. In most cases the source tables will be included but there are cases, especially when using CROSS APPLY - where SQL Server will not access the table because the outer query doesn't reference any of the columns.

And we also have derived tables - which are the same.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

Jeffrey Williams wrote:

This isn't quite correct - neither CTEs or Views are executed (materialized). Both will be incorporated into the outer query - and then the execution plan is generated.
Now, if you meant that the source tables are accessed each time a CTE/View is referenced - then that may be true depending on the usage and the execution plan that is generated. In most cases the source tables will be included but there are cases, especially when using CROSS APPLY - where SQL Server will not access the table because the outer query doesn't reference any of the columns.
And we also have derived tables - which are the same.

To be sure, I meant that a copy of the plan required for the CTE will be incorporated into the overall query execution plan for each separate reference which effectively means that the code for the CTE will be executed once for each reference including all that goes with that, such as what you mention.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

It would appear that the author of the article has left the building. 🙁

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

YSLGuru SSC-Insane Points: 21661 More actions · Answer 13

I thought Elvis was dead.

Kindest Regards,

Just say No to Facebook!