table variables vs. temp tables 1

[sqlcasey]

Hi all,

I have some questions about table variables vs. temp tables.

1.) Under what conditions would you chose one or the other?

2.) Is one faster than the other?

3.) Is this a valid replacement for using cursors?

Thanks much

 

[gmmastros]

1.) Under what conditions would you chose one or the other?

I almost always use table variables. In fact, the only time I (start off) using a temp table is when I am trying to capture the results of a stored procedure call.

2.) Is one faster than the other?

Yes. The problem is that it isn't always clear which one is faster. Usually, table variables will give you better performance, but not always. This is why I generally start off with table variables. If I can't get the query to run in an acceptable amount of time, then I will try using a temp table.

3.) Is this a valid replacement for using cursors?

It can be, depending on how you code it. To be truthful, I've always had a problem remembering the syntax for cursors. I find them a bit un-natural to work with.




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

Cursors baaaaad. But sometimes necessary.

Actually, I use the WHILE statement to avoid cursors.

We don't have too much of a call here to use Table variables. And I can remember the sytax for Temp tables much quicker. Especially as I tend to use Temp tables with SELECT INTO commands quite often.

The only problems I tend to have are in the tables I'm pulling the records from (very long and very wide) rather than access time due to the difference between Temp tables and Table variables.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[sqlcasey]

Thanks. Yes, I know cursors are "bad" but we have a developer here who seems addicted to them, so I'm always having to find ways around them.

Is there a FAQ here that describes workarounds for cursors? I seem to recall seeing this a while back.

Thanx

 

[SQLSister]

About your developer, sit him or her down and describe exactly why cursors are bad. Show examples of the time difference between the set-based solutions you had and the cursor that was orginally written. Suggest he refrain frmo using cursors.

If he persists, then tell him that every cursor that you fix from then on will be sent to the boss with a notation as to why it was causing a problem and how much performance was improved and who wrote the orginal code. And follow through.

If that doesn't clean up his act, then talk to his boss and get him to forbid the developer from using another cursor without going by you first to see if there is another solution.

Questions about posting. See faq183-874

 

[ShankarJ]

Hi!

Maybe this will be of interest :

http://www.sqlservercentral.com/columnists/kjambu/2691.asp

Regards

 

[Catadmin]

If you look at the FAQ section of this forum under T-SQL, there are several articles which should be able to help you out.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"