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 Monday, October 29, 2007 5:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:49 AM
Points: 1,391, Visits: 608
Check the following link for FYI:

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

:)



Post #415962
Posted Monday, October 29, 2007 6:02 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 @ 7:57 AM
Points: 43,045, Visits: 36,205
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 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 #415965
Posted Monday, October 29, 2007 6:13 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,

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.


--
Post #415972
Posted Monday, October 29, 2007 6:19 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 15,738, Visits: 28,146
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #415973
Posted Monday, October 29, 2007 6:25 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 @ 7:57 AM
Points: 43,045, Visits: 36,205
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... :D


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 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 #415977
Posted Monday, October 29, 2007 6:26 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,:)

I didn't mean that....seriously...:)

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.


--
Post #415979
Posted Monday, October 29, 2007 6:29 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,:)

Nice ...he he he ;)


Good day guys....tc

tomoro see you.....


Cheers!:P

Sandy


--
Post #415983
Posted Monday, October 29, 2007 8:25 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 15,738, Visits: 28,146
GilaMonster (10/29/2007)

Oooh, Grant, you've got a fan... :D

Oh that's all right. You have one too. :D


----------------------------------------------------
"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 #416038
Posted Monday, October 29, 2007 11:34 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
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.:)


--
Post #416296
Posted Tuesday, October 30, 2007 12:17 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 @ 7:57 AM
Points: 43,045, Visits: 36,205
My pleasure


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 #416307
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse