Monday, February 13, 2012

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

5 comments:

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

    ReplyDelete
    Replies
    1. 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. This comment has been removed by the author.

    ReplyDelete