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 Sunday, October 28, 2007 11:22 PM
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
Hi All,


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

Can anybody please clear me these three concepts
and when should i use this?

Also I wants to know how they are different from
each other in the terms of executing in SQL Server.


Cheers!

Sandy.


--
Post #415829
Posted Monday, October 29, 2007 1:09 AM


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 @ 3:52 AM
Points: 42,445, Visits: 35,501
I'll give this a try.

Temp tables

Behave just like normal tables, but are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that that proc calls.

Just like normal tables, they can have primary keys, constraints and indexes, and column statistics are kept for the table.

Temp tables, while they have space assigned to them in the tempDB database, will generally be accessed only from memory, unless the server is under memory pressure, or the amount of data in the table is large.

Table Variables

These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped.

Like with temp tables, table variables reside in TempDB. they have entries in the system tables in tempDB, just like temp tables, and they follow the same behaviour regarding whether they are in memory or on disk.

Table variables can have a primary key, but indexes cannot be created on them, neither are statistics maintained on the columns. This makes table variables less optimal for large numbers of rows, as the optimiser has no way of knowing the number of rows in the table variable.

CTE

In my experience, CTEs are more like temporary views than anything else. When you look at the execution plan, you'll see that they are inlined into the query, not materialised and stored. I find, with the exception of recursion, they're more to make queries simpler to write than faster to run.

Does that help?



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 #415849
Posted Monday, October 29, 2007 1:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: Banned Members
Last Login: Tuesday, July 22, 2014 3:58 AM
Points: 2,622, Visits: 327
Sandy,

Check out these examples and try to build your own functionality....

http://www.lakesidesql.com/articles/?p=12
Post #415851
Posted Monday, October 29, 2007 2:32 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 Gail Shaw,

Thanks a lot,

Can you please give more Idea about CTE,
and also its uses in SQL Server 2005.

Because its a HOT Topic (CTE) in SQL Server 2005.


VAIYDEYANATHAN.V.S,

Thanks for link, but Less information about CTE.
Can you please give some nice link for CTE?


Cheers!

Sandy


--
Post #415858
Posted Monday, October 29, 2007 2:39 AM


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 @ 3:52 AM
Points: 42,445, Visits: 35,501
What more do you want to know on CTEs? Here's a good, albeit fairly technical, article on CTEs.

http://blogs.msdn.com/craigfr/archive/2007/10/18/ctes-common-table-expressions.aspx



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 #415862
Posted Monday, October 29, 2007 3:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b305977&Product=sql2k also contrasts the advantages/disadvantages of table variables and temp tables.

Regards,
Andras




Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Post #415874
Posted Monday, October 29, 2007 4:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
Gail... nice explanation! Short, sweet, and to the point.

Sandy... think of a CTE the same as you would any "inline view" or "derived table" except that they are cited before the SELECT instead of in the FROM clause of a SELECT. Big advantage of CTE's is you can reference them more than once in the same SELECT.

Big advantage of a Temp table even over CTE's is that, once built, they can be referenced by multiple queries in the same proc... CTE's can only be referenced by the current query they are built for... just like derived tables.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #415905
Posted Monday, October 29, 2007 5:40 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 15,517, Visits: 27,897
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.

----------------------------------------------------
"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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #415950
Posted Monday, October 29, 2007 5:47 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 Jeff & Gail,

Gail As per you
:
These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped.


And as per Jeff
:
CTE the same as you would any "inline view" or "derived table" except that they are cited before the SELECT instead of in the FROM clause of a SELECT.


I wants to know If I will go for a "Select" Query for both what type of operation will going on in SQL Server?

As per Gail said that Table variable can't be with index and
CTE also, so Is it always go for table scan or not ?

Will be this an issue on my performance?

Because these both are not following the index scan concept.


Cheers!

Sandy.



--
Post #415956
Posted Monday, October 29, 2007 5:50 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 Grant,

Can you have a look on to my last post..

I will be happy if you and jeff will have a
sharp eye on my post.

Thanks a lot for your reply.


Cheers!

Sandy.


--
Post #415958
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse