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

Question

Post reply

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

Viewing 15 posts - 16 through 30 (of 50 total)

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 1

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.

--

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 2

Hey Gail,:)

Nice ...he he he 😉

Good day guys....tc

tomoro see you.....

Cheers!:P

Sandy

--

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

GilaMonster (10/29/2007)
Oooh, Grant, you've got a fan... 😀

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

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

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

--

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

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

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

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 7

IN_Sandeep

SSCertifiable

Points: 6259

October 30, 2007 at 6:30 am

#746601

Sure, Grant.:)

Cheers!

Sandy.

--

David Burrows SSC Guru Points: 65143 More actions · Answer 8

Gail... nice explanation! Short, sweet, and to the point.

Ditto 😀

What a nice friendly dialogue :w00t:

Must be painful with all that back slapping 😉

Far away is close at hand in the images of elsewhere.
Anon.

steve dassin SSCrazy Points: 2396 More actions · Answer 9

Sandy Millon. (10/28/2007)
I wants to clear my concept about Temp Table,
Table Variable & Common Table Expression Concept.

Some one is interested in a concept and not code? How interesting:)

If you understand the concept of the CTE you will understand the

concept of a table, a temporary table, a table variable in fact

anything that is referred to as a table in sql server. The underlying

concept is the same. You may find this interesting if you know little

of sql but something about computer science. If you know lots about

sql but little of computer science the concept requires some extra effort:)

Do you know what the Sql CTE is?

http://beyondsql.blogspot.com/2007/10/sql-whats-really-with-with.html

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 10

Hey roge,

Cool Link,

Thanks for Helping me...

Cheers!:)

Sandy.

--

Bhargav Patel SSC Journeyman Points: 89 More actions · Answer 11

Hello ,

CTE really improves performance as always in most cases. I would prefer to use CTE over Temp tables.

BHARGAV PATEL

yobhargav@yahoo.com

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

Bhargav Patel (7/1/2009)
CTE really improves performance as always in most cases.

Got some examples of that?

CTEs are pretty much 'named subqueries'. The whole query still runs in one go just as it would if the query defined in the CTE was in a subquery, it's not like a temp table where the interim results are stored somewhere and then reused.

Sometimes doing things all in one is faster. Sometimes dividing bits up and using temp tables is faster

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

hbpank SSC Eights! Points: 828 More actions · Answer 13

Hi Gail and Jeff,

Thanks a lot, quite informative

Hary

Thanks a lot,
Hary

WilliamBendall SSCrazy Points: 2591 More actions · Answer 14

Hi Bhargav Patel,

I'm not sure I'd agree with you. Temporary tables can be indexed, whereas CTE's cannot. It might be quicker in terms of coding turn around time, but performance I don't think so.

Cheers,

William

----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 15

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

Just to make clear, it isn't JUST about the number of rows you put into a table variable/temp table that matters. I can also be the actual VALUE(s) therein. For example, if you have a table variable with just 1 row, and field1's value is 'abc' and you join that table to another table on field1, you can get horrible performance if the data in the joined table is skewed such that a large percentage of the values are 'abc' due to nested loop plan. A temp table could have stats on field1 and discover that a hash/merge join would be much more efficient.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service