Estimated Execution Plan Temp Table

-
Estimated Execution Plan on Temp Table
Estimated Execution Plan with #TempTable
Estimated Execution Plan MS SQL Server

Recently I encountered a problem while trying to understand the performance issues with one of my stored procedures in SQL Server 2008.

This specific stored procedure uses a #tempTable to store some data. It looks like this:
Obviously I encountered "Invalid object name '#TempTable'."
I saw some places online which suggested several things, but the best solution for this was the simplest.

1. Simply comment the "drop table #tempTable" you do at the end of the stored procedure.
2. Run the query (which will save the #tempTable).
3. Click on the Estimated Execution Plan button.

Since you have data in your #tempTable, you can run it freely.

p.s.
Don't forget to drop this table when you're done.

Hope it helped,
Elad Shalom,
CTO at ITweetLive.com

Comments

  1. RippoMarch 15, 2012 at 12:40 PM

    or use a table variable and forget the #tmp table altogether declare @tmp table (id int, etc)

    ReplyDelete
    Replies
    1. AnonymousMarch 27, 2012 at 5:24 PM

      Yet sometime you can't avoid #tmp. If you inherited a stored procedure or have some awkward select with a long list of columns and a short amount of time.

      I agree variable tables are usually(!) better, but you sometimes have to find fixes and workarounds to deal with what you get.

      In this specific problem I had 0 time to find out what's wrong and I couldn't cought it with profiler/ sp_whosactive.

      Thanks for the comment though :)

      Delete
  2. RippoMarch 15, 2012 at 12:40 PM

    This comment has been removed by the author.

    ReplyDelete
  3. UnknownJuly 22, 2016 at 1:23 PM

    jordan shoes
    rolex replica watches
    fitflops sale clearance
    canada goose sale
    christian louboutin uk
    canada goose uk
    louis vuitton purse
    reebok outlet store
    lacoste shoes
    north face jackets
    nike air max 90
    coach outlet online
    air jordan shoes
    ray ban sunglasses outlet
    longchamp bag
    ugg boots
    instyler max
    true religion jeans outlet
    oakley sunglasses outlet
    louis vuitton handbags
    canada goose sale
    moncler uk
    coach outlet online
    cheap ray bans
    michael kors handbags
    dolce and gabbana outlet online
    jordan pas cher
    longchamp outlet store
    babyliss hair dryer
    nike store uk
    ecco outlet
    kate spade bags
    polo ralph lauren
    cheap ray ban sunglasses
    20160722caiyan

    ReplyDelete
  4. caiyanOctober 21, 2016 at 11:34 AM

    coach outlet
    nmd adidas
    adidas yeezy
    coach factory outlet
    ugg boots
    raybans
    michael kors outlet
    ugg outlet
    rolex daytona
    cheap oakleys
    20161021caiyan

    ReplyDelete
  5. UnknownMarch 14, 2017 at 11:04 PM

    cheap jordans
    adidas nmd
    christian louboutin heels
    oakley sunglasses
    louboutin outlet
    christian louboutin sneakers
    michael kors outlet online
    mizuno sneakers
    toms outlet
    adidas yeezy 350 boost
    2017.3.15chenlixiang

    ReplyDelete
  6. BensonJuly 15, 2019 at 12:31 AM

    fitflops sale clearance
    ferragamo belt
    bape hoodie
    hermes outlet online
    cheap jordans
    chrome hearts online
    coach outlet online
    kobe shoes
    michael kors
    off white jordan 1

    ReplyDelete

Post a Comment

Popular posts from this blog

Cloud Computing Advantages and Disadvantages

-
Cloud computing was introduced this week as a giant client server environment. Analyze the advantages and disadvantages of computing on the Internet. Cloud computing refers to anything that is involved in delivering host-based services on the Internet. The services are of three types: IaaS (Infrastructure-as-a-Service), PaaS (Platform-as-a-Service), and SaaS (Software-as-a-Service). The term ‘cloud computing’ is derived from the symbol of the cloud that often describes the Internet in flowcharts and diagrams.  Recently, cloud computing has become one of the most hyped information technology topics of the decade (Enslin 2012). With cloud computing one can access documents and applications from any part of the world. This frees you from being bound to your computer at home. Miller says that cloud computing is definitely not for everyone (Miller 2009). There are advantages and disadvantages to this type of internet computing. While analyzing the advantages, Miller says compu
Read more

Linked Files in Visual Studio 2010

-
Image
File Linking, File Sharing and Shared Projects. You know how it feels to have shared files on VSS? Gooood . Actually it felt so good that my beloved CTO (who said something about promotion?) switched to TFS just 4-5 months ago.  And ever since - I cry myself to sleep every night. Instead of one file, shared between 5,6,7 or 10 projects, we now have a copied file for each project.  This is due to Microsoft "Best Practice" which says - No Shared Files . Meaning...? If I have a JavaScript file called Arrays.js.  It's all purpose is to handle arrays in JavaScript.  This file exists in 6 different projects, which mean, that if I have another addition to this small JavaScript library, I have to copy it to 6 different files in 6 different projects... JUST SHOOT ME!!! Finally, I have the fix we've all been waiting for, courtesy of VS 2010: "Add File As Link" How to Link Files to Different Projects: Let’s say we have 2 completely dif
Read more

BDD, TDD, and Everything Between

-
Behavior Driven Development, Test Driven Development, and Everything Between What is TDD (Test Driven Development) Test Driven Development was introduced by Kent Beck, in 2003.  This followed the concepts of Extreme Programming, introduced in 1999 with a development experiment done by both IBM and Microsoft. The purpose of the Test Driven Development is to make sure code is clear, tested, and as redundant as possible, by making sure the tests are written first, and code is being added to "fill the blanks".  Every code iteration needs to pass all tests (may those be unit tests, integration tests, data integrity tests, or UI tests). Writing the tests first allow us to see what fails, how, and allow us to visualize the structure of our code, by making sure each test is performed to test a specific, extremely defined sub-section of a feature. Let's assume a "BasicMaths" class, to perform simple mathematics operations. [TestClass]   public   class  Uni
Read more