Click here to get free chapters (PDF) in the mailbox
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 (https://blog.sqlauthority.com)
8 Comments. Leave new
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.
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-
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.
what is the difference between temporary table and table variable
They are available in addtional notes
what is the difference between sql server2005 and sql server 2008?
Search for “What is new in SQL Server 2008?” in Microsoft site
3