SQL SERVER – Debate – Table Variables vs Temporary Tables – Quiz – Puzzle – 13 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is an article which discusses the Author’s Perspective.

Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.

SQL Quiz

Some questions are good for debates. There are so many myths associated with a few subjects that when the discussion starts, it is hard to convince a stubborn friend who is not ready to accept the truth. Let me see you attempt both sides of the following question: What is the difference of table variable and temp tables?

Link to participate in SQL Quiz

Notes of Vinod Kumar

Understanding these special tables is an important thing, although the usages can be a fine line and are often interchangeably used. What are the differences in scope, usage restrictions and more?

Notes of Pinal Dave

This is indeed an eternal debate. I often meet developers who have different beliefs and strong opinions about when to use what. I frequently express developers the internals as well as what the optimal solution between Table Variables and Temporary Tables is, considering their situation. However, I always feel the desire to reach to every single developer and explain the solution in depth. Here is an advice for those who are confused what to use and what. Test it, compare it and implement it. I always suggest to developers to trust SQL Server Engine and test their solution against it and implement it in their application.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#75-76
Difference Temp Table and Table Variable – Effect of Transaction
Difference TempTable and Table Variable – TempTable in Memory a Myth
ObjectID in Negative – Local TempTable has Negative ObjectID

Prize

There are exciting prizes awaiting the winners. Click here for Prizes and Frequently Asked Questions.

Link to participate in SQL Quiz

Note: The SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.

Reference: Pinal Dave (http://blog.sqlauthority.com)

11 thoughts on “SQL SERVER – Debate – Table Variables vs Temporary Tables – Quiz – Puzzle – 13 of 31

  1. Hi Dave, it seems every time I google a SQL issue, your site comes up, and it’s always very informative. If it doesn’t answer my problem directly, it normally points me in the right direction. I’m not a DB admin, and i’m not formally educated in database design, rather I only have what my years of forcing it to work have given me.
    This week my issue is temp tables. I seem to have a problem with SQL express such that if my temp table goes beyond a certain size, it takes the server forever to get it done. This is very odd because I’m using something like

    SELECT INTO #temp FROM

    but what’s odd is that the takes about a second to retun 200 rows, is about 18 columns of ints, varchar(50)’s, and numeric(16,4)’s, no identity columns, indexes, or anything like that. It takes 2-3 minutes to generate and insert in to the tempt table. The execution plan reflects that all of the time is spent on the INSERT. The same thing happens if I switch to a table variable. I would include specifics, but I really think it has something to do with external factors from the statement itself. I’ve found a few forums with people having a similar issue, but no one helping them has been able to recreate it, leading me to belive it’s an environmental situation, and not the specific SQL.

    Anyhow, if you’ve ever heard of anything like this, where an INSERT into a temp table takes an abnormal amount of time for a handfull of records, I’d love to know what it is. Thanks Dave.

    Like

  2. I just noticed that the forums remove my angle bracket code, making my above post illegible. my SQL statement was supposed to be
    SELECT -FIELDLIST- INTO #Temp FROM -SQLQUERY-

    Like

  3. On a whim I tried:
    DBCC DropCleanBuffers
    DBCC FreeProcCache

    and that actually fixed it. I don’t want to do this every single time, as that would probably reduce my preformance in other areas. Any additional knowledge on why this might fix it would be welcome, as I could maybe put a more permenent fix in place, but at least things are moving forward again.

    Like

  4. Pingback: SQL SERVER – Table Variables and Transactions – SQL in Sixty Seconds #007 – Video « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – Using MAXDOP 1 for Single Processor Query – SQL in Sixty Seconds #008 – Video « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #012 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s