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 (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 1255
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.

--
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89287 Visits: 45284
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, 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


IN_Sandeep
IN_Sandeep
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 1255
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

--
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89287 Visits: 45284
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, 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


Andras Belokosztolszki
Andras Belokosztolszki
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2319 Visits: 1585
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88404 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41133 Visits: 32666
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
IN_Sandeep
IN_Sandeep
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 1255
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.

--
IN_Sandeep
IN_Sandeep
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 1255
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.

--
Kishore.P
Kishore.P
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1725 Visits: 619
Check the following link for FYI:

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

Smile



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