Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Temp Table 'vs' Table Variable 'vs' CTE. Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2007 6:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 14,201, Visits: 28,530
Nice job Gail.

Talk to you later Sandy.


----------------------------------------------------
"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
Post #416450
Posted Tuesday, October 30, 2007 6:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 7, 2011 3:15 AM
Points: 375, Visits: 1,255
Sure, Grant.:)

Cheers!

Sandy.


--
Post #416467
Posted Tuesday, October 30, 2007 7:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 7,056, Visits: 7,279
Gail... nice explanation! Short, sweet, and to the point.


Ditto :D

What a nice friendly dialogue

Must be painful with all that back slapping ;)



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

Anon.

Post #416510
Posted Wednesday, October 31, 2007 12:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 31, 2010 6:02 PM
Points: 174, Visits: 2,075
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 interesting:)
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 effort:)

Do you know what the Sql CTE is?
http://beyondsql.blogspot.com/2007/10/sql-whats-really-with-with.html
Post #416885
Posted Wednesday, October 31, 2007 12:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 7, 2011 3:15 AM
Points: 375, Visits: 1,255
Hey roge,

Cool Link,

Thanks for Helping me...


Cheers!:)

Sandy.


--
Post #416887
Posted Wednesday, July 1, 2009 12:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 25, 2013 4:16 PM
Points: 1, Visits: 182
Hello ,

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

BHARGAV PATEL
yobhargav@yahoo.com
Post #745673
Posted Wednesday, July 1, 2009 12:22 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
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 2008, MVP
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

Post #745683
Posted Thursday, August 20, 2009 4:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:10 PM
Points: 30, Visits: 223
Hi Gail and Jeff,

Thanks a lot, quite informative

Hary


Thanks a lot,
Hary
Post #774087
Posted Thursday, August 20, 2009 5:24 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 14, 2011 3:01 AM
Points: 95, 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
Post #774123
Posted Friday, August 21, 2009 10:59 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 4,469, Visits: 6,398
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 at GMail
Post #775265
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse