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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213827 Visits: 46262
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
IN_Sandeep
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1255
Hey Gail,Hehe

I apology for that...BigGrin

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

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!Tongue

Sandy.

--
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94519 Visits: 33010
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213827 Visits: 46262
Sandy (10/29/2007)
Hey Gail,
I apology for that...BigGrin
I didn't mean that.(he he he..lolz)


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


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


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


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!Tongue

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


IN_Sandeep
IN_Sandeep
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1255
Hey Grant,Smile

I didn't mean that....seriously...Smile

4 tat I already told to Gail,

I wants every body's opinion on my topic,,

I would like to see view by John and Steve too....


Cheers!

Sandy.

--
IN_Sandeep
IN_Sandeep
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1255
Hey Gail,Smile

Nice ...he he he Wink


Good day guys....tc

tomoro see you.....


Cheers!Tongue

Sandy

--
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94519 Visits: 33010
GilaMonster (10/29/2007)

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

Oh that's all right. You have one too. BigGrin

----------------------------------------------------
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1255
GilaMonster (10/29/2007)
--------------------------------------------------------------------------------
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)


Thanks a Lot Gail, I cleared my concepts.

Grant (10/29/2007)
--------------------------------------------------------------------------------

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.


Thanks Grant,

Today morning I just tried with this SQL script
I got more idea,

1st
====
Begin

Declare @abc Table
(
a int
)

insert into @abc
select a from t1 -- this is going for index scan for t1 Table

select * from @abc -- this is going for table scan from @abc Table Variable.

end
------

2nd
====

with abc
as
( select a from t1 )

select * from abc -- it is only going for Index scan for t1 Table
not for abc , which is not a table means inline view.

Thanks to both of you and others those clear my concepts..

Have a Nice Day...!!!

Cheers!

Sandy.Smile

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

Group: General Forum Members
Points: 213827 Visits: 46262
My pleasure

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


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94519 Visits: 33010
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
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