Table Variable vs Temp Table, 70-451 preparation questions

Question

Table Variable vs Temp Table, 70-451 preparation questions

tuseau

SSC-Addicted

Points: 487
More actions
May 22, 2011 at 9:11 am

#250684

I'm studying for my 70-451 exam, and doing some practice tests. One question in the practice test, two answers are identical, except one uses a table variable and another uses a temp table. In the situation they present, it would make no functional difference which one to use, but all my experience would say that a temp table would be faster. However, the "correct" answer in the test is apparently to use a table variable, not a temp table. This seems to me both wrong and ridiculous since it is such a subjective choice. Is Microsoft's "official" line to recommend the use of a table variable whenever possible over a temp table? Because if that's the case, it seems they don't understand their own product very well!

Viewing 11 posts - 1 through 10 (of 10 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

It depends on the scenario.

There are cases where a table variable would be preferred over a temp table (e.g. to capture some values even after a rollback). Without knowing the scenario in detail it's hard to tell whether it's wrong or not.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

LutzM (5/22/2011)
It depends on the scenario.
There are cases where a table variable would be preferred over a temp table (e.g. to capture some values even after a rollback). Without knowing the scenario in detail it's hard to tell whether it's wrong or not.

+1. Depends entirely on the business needs and amount of data and how you use that table.

I've seen 100 to 1 speed improve by changing 2-3 table variables to temp tables in join scenarios so that definitly destroys the always table variable rule here.

WayneS SSC Guru Points: 95460 More actions · Answer 3

You might want to look at this article: Comparing Table Variables to Temporary Tables[/url]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

tuseau (5/22/2011)
However, the "correct" answer in the test is apparently to use a table variable, not a temp table. This seems to me both wrong and ridiculous since it is such a subjective choice. Is Microsoft's "official" line to recommend the use of a table variable whenever possible over a temp table? Because if that's the case, it seems they don't understand their own product very well!

You've identified the very reason why I hate such exams. I'm convinced that some of the test writers haven't spent an actual day "on the line" with SQL Server. I won't go into what I think about the folks that write "trick" questions.

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

tuseau SSC-Addicted Points: 487 More actions · Answer 5

I get the feeling that Microsoft are keen to emphasize their "new" additions in the exams - hence favoritism towards table variable over temp tables, CTEs over subqueries, etc. I've found that CTEs are often chosen as the only "correct" answer where a subquery would be equally correct. In fact in my experience as a developer, nested CTEs have awful performance compared to nested subqueries.

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

Bear in mind that practice exams (MeasureUp, Transcend, etc) are not written by MS. They're written by independent companies based on the MS provided list of what's covered by the exam.

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

GilaMonster (5/22/2011)
Bear in mind that practice exams (MeasureUp, Transcend, etc) are not written by MS. They're written by independent companies based on the MS provided list of what's covered by the exam.

Understood. With that in mind, have you taken any of the MS exams (apoligies if I'm overlooking any certs you may have)? If so, do you find the questions straightforward or are there questions like those described on this post?

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

pietlinden SSC Guru Points: 63512 More actions · Answer 8

FWIW, I took the SQL 2005 Developer cert exams. The Measure Up exams were significantly more difficult. (And realistic).

I'm really not sure having certs proves anything at all. I know several people here in the local UG who have zero certifications, but know SQL Server inside and out. I think they're okay if you don't have experience, but experience trumps certs any day.

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

Jeff Moden (5/22/2011)
Understood. With that in mind, have you taken any of the MS exams (apoligies if I'm overlooking any certs you may have)? If so, do you find the questions straightforward or are there questions like those described on this post?

Err, yes. All of the SQL certs going back to 2000 (excluding BI ones).

The questions are not straightforward memorisation (what is the command to rebuild an index). They are in general scenario based. e.g. Given X, Y, Z and the additional requirement of C, which of the options is best? Which of the following do you need to do to configure M?

I've seen a couple ambiguous questions, where there wasn't a clear right answer (but then I've written most as beta exams, so that can be expected) I've not seen an out-and-out trick question (one that you cannot answer without knowing what the test writer was thinking)

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

tuseau SSC-Addicted Points: 487 More actions · Answer 10

Thanks for the input everyone - I had a look at the article "Comparing Table Variables to Temporary Tables", and it is useful.

I suppose it is true that this was a practice exam, not a MS exam question, so that is certainly worth taking into account. Hopefully the real thing will not have such vague and subjective questions.