SQL Server Quickie #32 – Temp Tables vs. Table Variables

February 1, 2016 · Klaus Aschenbrenner · 2 Comments

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Today I have uploaded SQL Server Quickie #32 to YouTube. This time I’m talking about the difference between Temp Tables and Table Variables in SQL Server.

Like or share to get the source code.

Thanks for your time,

-Klaus

2 Comments

golam kabir
02/04/2016

good as usual

Reply
Frederik Vanderhaegen
02/15/2016

I think when you execute the stored procedure multiple times, you’ll see there is only a recompilation the first time.
The second time nothing will be recompiled.

Reply

It`s your turn Cancel reply

SQLpassion Workshops

SQLpassion Online Academy

Self-Paced Online Trainings
September 12, 2017: Troubleshooting Deadlocks in SQL Server
September 14, 2017: SQL Server Availability Groups

SQL Server Query Tuning Workshop

November 6 – 10, 2017 in Antwerpen/Belgium

SQL Server Performance Tuning & Troubleshooting Workshop

November 20 - 24, 2017 in Zurich/Switzerland
December 4 - 8, 2017 in Frankfurt/Germany

bolte

SQL First Aid Kit
Sign up for free

Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan – you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server:

Here is some feedback

Bob from Zoetermeer, Netherlands

"The SQL Server Query Tuning Workshop helped me as a developer to realize how important a good indexing strategy is for a responsive application. In my opinion every developer, working with SQL Server, should know how the indexes are build up, and how the query optimizer is getting to his query plan."

5.0

2015-01-22T12:38:33+00:00

https://www.sqlpassion.at/archive/testimonials/bob-from-zoetermeer/

Roger from Hertogenbosch, Netherlands

"During the workshop I have learned a lot about the query optimizer internals and how I can use that knowledge to write optimal performing queries. Klaus obviously knows a lot about these topics and is able to teach them very well. I can’t wait to attend one of his workshops in the near future."

5.0

2015-01-13T15:28:08+00:00

https://www.sqlpassion.at/archive/testimonials/roger-from-hertogenboschnetherlands/

Thomas from St. Margrethen, Switzerland

"I was lucky enough to attend the 4 day long SQL Server Query Tuning Workshop from Klaus in Zurich. He has presented in a very easy way the performance related internals of SQL Server that will help you to write better performing SQL queries. Based on that knowledge I finally know now why sometimes queries are fast, and sometimes are slow. It was an amazing workshop, which makes the day-to-day work with SQL Server now very easy."

5.0

2015-01-13T15:23:09+00:00

https://www.sqlpassion.at/archive/testimonials/thomas-from-st-margrethenswitzerland/

Arun from London, United Kingdom

"My day-to-day writing of SQL has gone up a level – I am able to finely tune individual statements or underlying database objects to get the kind of query plan I want. Moreover I know now what sort of plan I am looking for. I have been already able to use this knowledge to address performance issues that came up this week in capacity testing."

5.0

2015-01-13T15:19:20+00:00

https://www.sqlpassion.at/archive/testimonials/arun-from-londonunited-kingdom/

Bernd from Monheim, Germany

"After attending the 4 days long workshop you know very exactly how SQL Server executed SQL queries on the logical and physical level through execution plans. In addition you will know how you can improve queries by applying the right indexing strategy. This in-deep knowledge was very well explained and presented by Klaus."

5.0

2015-01-13T15:22:12+00:00

https://www.sqlpassion.at/archive/testimonials/bernd-from-monheimgermany/

Ina from Oberhaching, Germany

"Anyone faced with the task to implement queries on complex ERP data structures – for reporting, providing views, or for statistical analysis – comes quickly to its limits without knowledge of the operation of SQL Server. The workshop provides an excellent insight into the technical world of SQL Server, explained with many examples of queries and their optimization."

5.0

2015-01-13T15:21:05+00:00

https://www.sqlpassion.at/archive/testimonials/ina-from-oberhachinggermany/

Filip from Beerse, Belgium

"This training on SQL Server performance tuning was extremely interesting. Klaus taught me lot of things I was not aware of. I especially loved the tips he gave on common mistakes and wrong concepts about SQL Server. Such information, coming from an independent expert (“expert” is still very understimated when talking about Klaus’ knowledge), is a lot more valuable than spending hours reading and googling. This training has a high return on investment and anyone who is seriously working with SQL Server should attend this workshop."

5.0

2015-01-13T15:13:34+00:00

https://www.sqlpassion.at/archive/testimonials/filip-from-beersebelgium/

Wim from Heverlee, Belgium

"The SQL Server Performance Tuning & Troubleshooting Workshop exceeded the high expectations I already had. All the separate bits and pieces of SQL knowledge we gathered through the years, were properly tied together by Klaus. He provided us with all the necessary knowledge to tackle the performance issues we will surely encounter in our day to day business. Highly recommended!"

5.0

2015-01-13T15:08:29+00:00

https://www.sqlpassion.at/archive/testimonials/wim-from-heverleebelgium/

Carla from Heverlee, Belgium

"The workshop exceeded my expectations! The complete workshop was well structured, all topics were explained very clear with a lot of excellent prepared and documented demos. I definitely will use the knowledge and documentation that Klaus shared with us during the workshop as starting point and guide for my future work."

5.0

2015-01-13T15:10:40+00:00

https://www.sqlpassion.at/archive/testimonials/carla-from-heverleebelgium/

Sedigh from Veenendaal, Netherlands

"I found the SQL server Performance Tuning workshop very useful. No matter you are a DBA, developer or tester, Klaus explains the materials the way that it is understandable for everyone. I recommend this workshop, it worths 5 stars plus!"

5.0

2015-04-21T18:47:32+00:00

https://www.sqlpassion.at/archive/testimonials/sedigh/

Adrian from London, United Kingdom

"It does not matter how long you have been working with databases, there is so much in this course that you are guaranteed to learn a great deal. I have been working with databases for 20 years and I have never gained as much knowledge of database internals in such a short period. I have been searching for some time for a course like this and this is packed with valuable techniques and tips."

5.0

2015-07-03T09:49:03+00:00

https://www.sqlpassion.at/archive/testimonials/adrian-from-londonuk/

Michael from Stuttgart, Germany

"Klaus presented his SQL Server Performance Tuning Workshop on a very technical and understandable level. All topics of the agenda were covered and structured in an excellent way. Klaus demonstrates with his examples complex topics in a very simple way. What can you use from this workshop in your day-to-day life? Everything!"

5.0

2015-11-17T16:09:19+00:00

https://www.sqlpassion.at/archive/testimonials/michael-from-stuttgart-germany/

Dieter from Kirchheim-Heimstetten, Germany

"The 5 days were very interesting and informative for me. The various subjects were presented by Klaus in a very clear way without getting bored. Every possible question was answered by Klaus with a great explanation. I use now the acquired knowledge in my daily work to optimize my SQL Server installations."

5.0

2015-11-26T10:01:11+00:00

https://www.sqlpassion.at/archive/testimonials/dieter-from-kirchheim-heimstetten-germany/

Markus from Diepoldsau, Switzerland

"In the company I am working for, I have to guarantee the 100% availability of our ERP system and to increase its performance. With this outstanding course I am now even more able to master these tasks day-by-day. More than before I can now identify bottlenecks and provide solutions to eliminate them."

5.0

2015-12-08T14:11:01+00:00

https://www.sqlpassion.at/archive/testimonials/markus-from-diepoldsau-switzerland/

Claudio from Stäfa, Switzerland

"The workshop was very well structured, a very good mix between theory and practical examples. I had a lot of light bulb moments and it gave me a much better feeling for the practical techniques that can be used in the field and how things are related. I’m looking forward to apply what I’ve learned in the near future. Make no mistake, the course and topics are challenging, but your effort will be rewarded!"

5.0

2015-12-08T14:12:50+00:00

https://www.sqlpassion.at/archive/testimonials/claudio-from-stafa-switzerland/

Michail from Rotkreuz, Switzerland

"Five days of pure SQL knowledge were described in excellent manner. The day after I was able to understand the behavior of my databases and its execution plans in a much better way. I also know how important a good index strategy can be and how I can handle the locking und blocking behavior within my databases. Klaus explained all the necessary knowledge in a simple way and showed everything with a lot of good examples. Thank you."

5.0

2015-12-11T10:03:34+00:00

https://www.sqlpassion.at/archive/testimonials/michail-from-rotkreuz-switzerland/

Siegfried from Munich, Germany

"A lot of important information were presented in the five days. Klaus knows every question an answer and has it always demonstrated with a concrete example. For all people who have performance problems with SQL Server, the workshop is a recommendation!"

5.0

2015-12-18T07:45:12+00:00

https://www.sqlpassion.at/archive/testimonials/siegfried-from-munich-germany/

Mark from Montfoort, Netherlands

"In this workshop Klaus manages to cover everything you need to know about the SQL Server engine. With real-life examples it also becomes easy to follow.
I would highly recommend this course to every DBA or developer."

5.0

2016-04-18T14:48:31+00:00

"In this workshop Klaus manages to cover everything you need to know about the SQL Server engine. With real-life examples it also becomes easy to follow. I would highly recommend this course to every DBA or developer."

https://www.sqlpassion.at/archive/testimonials/mark-from-montfoortnetherlands/

5.0