SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temp Table 'vs' Table Variable 'vs' CTE.


Temp Table 'vs' Table Variable 'vs' CTE.

Author
Message
IN_Sandeep
IN_Sandeep
SSChasing Mays
SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)

Group: General Forum Members
Points: 611 Visits: 1255
Sure, Grant.Smile

Cheers!

Sandy.

--
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9554 Visits: 9752
Gail... nice explanation! Short, sweet, and to the point.


Ditto BigGrin

What a nice friendly dialogue w00t

Must be painful with all that back slapping Wink


Far away is close at hand in the images of elsewhere.

Anon.


steve dassin
steve dassin
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
Points: 274 Visits: 2077
Sandy Millon. (10/28/2007)

I wants to clear my concept about Temp Table,
Table Variable & Common Table Expression Concept.


Some one is interested in a concept and not code? How interestingSmile
If you understand the concept of the CTE you will understand the
concept of a table, a temporary table, a table variable in fact
anything that is referred to as a table in sql server. The underlying
concept is the same. You may find this interesting if you know little
of sql but something about computer science. If you know lots about
sql but little of computer science the concept requires some extra effortSmile

Do you know what the Sql CTE is?
http://beyondsql.blogspot.com/2007/10/sql-whats-really-with-with.html
IN_Sandeep
IN_Sandeep
SSChasing Mays
SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)SSChasing Mays (611 reputation)

Group: General Forum Members
Points: 611 Visits: 1255
Hey roge,

Cool Link,

Thanks for Helping me...


Cheers!Smile

Sandy.

--
Bhargav Patel
Bhargav Patel
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 183
Hello ,

CTE really improves performance as always in most cases. I would prefer to use CTE over Temp tables.

BHARGAV PATEL
yobhargav@yahoo.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87205 Visits: 45271
Bhargav Patel (7/1/2009)

CTE really improves performance as always in most cases.


Got some examples of that?

CTEs are pretty much 'named subqueries'. The whole query still runs in one go just as it would if the query defined in the CTE was in a subquery, it's not like a temp table where the interim results are stored somewhere and then reused.
Sometimes doing things all in one is faster. Sometimes dividing bits up and using temp tables is faster

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


hbpank
hbpank
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 225
Hi Gail and Jeff,

Thanks a lot, quite informative

Hary

Thanks a lot,
Hary
WilliamBendall
WilliamBendall
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 308
Hi Bhargav Patel,

I'm not sure I'd agree with you. Temporary tables can be indexed, whereas CTE's cannot. It might be quicker in terms of coding turn around time, but performance I don't think so.

Cheers,
William

----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12423 Visits: 8548
Grant Fritchey (10/29/2007)
Everyone else has covered this for the most part, but I don't mind adding one bit of information. Table variables, unlike temporary tables, do not have statistics created on them. This means that they are treated as if they have one row for any joins or searches done against them. When they only have a few rows, this doesn't matter, but when they have hundreds or thousands of rows, it makes a huge difference in the execution plans and the performance. It's just something to keep in mind.


Just to make clear, it isn't JUST about the number of rows you put into a table variable/temp table that matters. I can also be the actual VALUE(s) therein. For example, if you have a table variable with just 1 row, and field1's value is 'abc' and you join that table to another table on field1, you can get horrible performance if the data in the joined table is skewed such that a large percentage of the values are 'abc' due to nested loop plan. A temp table could have stats on field1 and discover that a hash/merge join would be much more efficient.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39759 Visits: 32639
TheSQLGuru (8/21/2009)

Just to make clear, it isn't JUST about the number of rows you put into a table variable/temp table that matters. I can also be the actual VALUE(s) therein. For example, if you have a table variable with just 1 row, and field1's value is 'abc' and you join that table to another table on field1, you can get horrible performance if the data in the joined table is skewed such that a large percentage of the values are 'abc' due to nested loop plan. A temp table could have stats on field1 and discover that a hash/merge join would be much more efficient.


You sure about that one? I mean the optimizer assumes one row. If it has one row, it's pretty likely to be able to come up with a plan that uses one row correctly, no? That's, to a degree, regardless of what's in the row.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search