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

Question

Post reply

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

IN_Sandeep

SSCertifiable

Points: 6259
More actions
October 28, 2007 at 11:22 pm

#178808

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

Viewing 15 posts - 1 through 15 (of 50 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 2

Sandy,

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

http://www.lakesidesql.com/articles/?p=12

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 3

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

--

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 SSC-Insane Points: 21971 More actions · Answer 5

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 SSC Guru Points: 1003851 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 7

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 8

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 SSCertifiable Points: 6259 More actions · Answer 9

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 SSCrazy Eights Points: 8147 More actions · Answer 10

Check the following link for FYI:

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

🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Common Table Expressions, despite their name, are not tables. Think of them as a temporary view that you can use to simplify a query. The 'temporary view' lasts for just one statement.

The following two queries are equivalent (and have the same execution plan)

Based off AdventureWorks

Without a CTE

SELECT SalesOrderNumber, PurchaseOrderNumber, TotalPerOrder FROM Sales.SalesOrderHeader soh INNER JOIN

(SELECT COUNT (*) TotalPerOrder, SalesOrderID FROM Sales.SalesOrderDetail sd GROUP BY sd.SalesOrderID) Sub

ON soh.SalesOrderID = sub.SalesOrderID

With a CTE

;WITH Sub (totalPerOrder, SalesOrderID) AS (

SELECT COUNT (*) TotalPerOrder, SalesOrderID FROM Sales.SalesOrderDetail sd GROUP BY sd.SalesOrderID

)

SELECT SalesOrderNumber, PurchaseOrderNumber, TotalPerOrder

FROM Sales.SalesOrderHeader soh INNER JOIN Sub ON soh.

What I did was to take the subquery in the first, and turn it into a CTE in the second.

All that's happened when the outer query executes, is that the definition of the CTE is placed into the query, replacing reference to the CTE, essentially becomming an inline subquery

There's no temp table of any form involved, hence no concerns regarding indexing or table scans.

Run those 2 in adventureworks with the execution plan on, and you'll see how they run.

Sandy (10/29/2007)
Hey Grant,
I will be happy if you and jeff will have a
sharp eye on my post.

So I needn't have replied?

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 SSCertifiable Points: 6259 More actions · Answer 12

Hey Gail,:hehe:

I apology for that...:D

I didn't mean that.(he he he..lolz)

Actually Grant is one of my Fav member in this site.

I can say this you are awesome, when you are replying me,

and you are different than others.....:)

I will be here tomoro to discuss more about this topic to make myself clear till I am not sure, I am not going to leave you people...

Still I am not sure about Table Variable and CTE....wrt to Table Scan or Index Scan......

Cheers!:P

Sandy.

--

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 13

Sandy (10/29/2007)
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.

Hey Sandy,

Don't limit your options, Gail is better at this stuff than I am.

You need to think about these as different things with different functions. CTE is an extremely neat and clean way to create a multi-use derived table, a temporary view as others defined it. Table variables and temporary tables are methods for persisting data temporarily. Neither directly assists in querying the way a CTE does. Usually they're used when you need to some sort of multi-step manipulation or share data between processes or something along those lines. But again, when the time comes to query the data out of a table variable or temp table, there are very distinct differences in behavior that, depending on the amount of data, makes temp tables perform better than table variables.

Cheers.

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Sandy (10/29/2007)
Hey Gail,
I apology for that...:D
I didn't mean that.(he he he..lolz)

Is fine. I was fairly sure you didn't 😉

Actually Grant is one of my Fav member in this site.

Oooh, Grant, you've got a fan... 😀

I will be here tomoro to discuss more about this topic to make myself clear till I am not sure, I am not going to leave you people...
Still I am not sure about Table Variable and CTE....wrt to Table Scan or Index Scan......
Cheers!:P
Sandy.

We'll be here.

As for CTEs and index/table scans. As I said, CTEs aren't tables. They're temporary views. As such, they them selves cannot be the subject of index or table scans. The tables referenced by the CTE are the ones that are read (either by scan or seek)

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