What is the RAM ramification of CTE?

Question

What is the RAM ramification of CTE?

Viewing 5 posts - 16 through 20 (of 20 total)

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

keepintouch2b Say Hey Kid Points: 682 More actions · Answer 1

Thanks all who have replied.

To sum it up, "In general, you use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table. In that case, you can create indexes on the temporary table to increase query performance. However, each scenario may be different. Microsoft recommends that you test if table variables are more helpful than temporary tables for a particular query or stored procedure."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

keepintouch2b (3/31/2010)
Thanks all who have replied.
To sum it up, "In general, you use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table. In that case, you can create indexes on the temporary table to increase query performance. However, each scenario may be different. Microsoft recommends that you test if table variables are more helpful than temporary tables for a particular query or stored procedure."

Actually, no one has said that. Which ever you use, you need to test completely. Table variables may work great in some instances and terrible in others. There are trade offs with each option you take.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

I'd argue that in general

"Be familiar with the advantages and disadvantages of table variables and temp tables and use whichever is appropriate to the situation"

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150468 More actions · Answer 4

"It depends"™

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

As Gail and Paul said, it depends.

And, you are welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events