THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

TSQL Tuesday #8: Those who can, do, and those who want to learn more, teach!

It's time for the eighth T-SQL Tuesday, managed this time by Robert Davis at SQL Server Central. This time, the topic was announced very late, and I was just starting to write a blog post of my own when I saw it. Fortunately, my topic easily fits into this month's topic of "Gettin' Schooled", which is supposed to have something to do with either teaching or learning. I feel very lucky, because in my work, I get to do both, at the same time! Many, if not most, of the really cool things I like to tell people about, I learned because questions asked of me by my students.

In my online seminar two weeks ago, I was talking about the difference between estimated and actual execution plans. The simple explanation of the difference is that with an estimate plan (like you get with SET SHOWPLAN_ALL ON), your batch is not executed, but with an actual plan (like you get with SET STATISTICS PROFILE ON), your batch IS executed and results are returned. I talked about when the actual plan might be different than the estimated plan. Although I'm not going to go into all the differences now, I will tell you that for batches of a single query, your estimated and actual plans should be the same. Differences can come up when you have a multi statement batch, or a batch that calls a procedure.

One example that I always mention in classes and seminars is the case where the batch creates a temp table, and then uses that temp table. When trying to look at the estimated plan, you will get an error, because the batch is not executed. If it is not executed, no temp table is created, so the statement that selects from the temp table cannot even be compiled to give a plan:

-- I am using a table called Test1 to get the data for my temp table;
-- You can use any table of your own

SET SHOWPLAN_ALL ON;
GO
SELECT ID INTO #Tmp1
FROM Test1;
SELECT *
FROM #Tmp1;

The above will give the following error:

Msg 208, Level 16, State 0, Line 3
Invalid object name '#Tmp1'.

However, looking an actual plan will not:

SET SHOWPLAN_ALL OFF;
GO
SET STATISTICS PROFILE ON;
GO
SELECT ID INTO #Tmp1
FROM Test1;
SELECT *
FROM #Tmp1;

When discussing this example during the online seminar, one of the participants asked why the behavior was different with a table variable. And I realized, I had never tried my example with a table variable. After the seminar was over, I ran some tests, and sure enough, there was no error looking at an estimate plan when a table variable was created.

-- I am still using a table called Test1 to get the data
-- However, in this case, you'll need a table with a single INT column

SET STATISTICS PROFILE OFF;
GO
SET SHOWPLAN_ALL ON;
GO
DECLARE @Tmp1 TABLE (ID int);
INSERT INTO @Tmp1(ID)
  SELECT ID
  FROM Test1;
SELECT * FROM @Tmp1;

-- No error is returned!

I started searching online to see if anyone had an explanation, and found this blog post written by Lubor Kollar and Lindsey Allen of the SQLCAT Team at Microsoft. (The blog post also contains a script for building the Test1 table, if you want to copy it and execute it.)

Lubor and Lindsey are discussing different execution plans when using temp tables and table variables, but when I looked closer, there weren't ever getting errors, even with a temp table. It turns out their temp table was created with CREATE TABLE statement, rather than SELECT INTO, and that completely changes how the batch is processed:

SET SHOWPLAN_ALL ON;
GO
CREATE TABLE #Tmp1 (ID int);
INSERT INTO #Tmp1(ID)
  SELECT ID
  FROM Test1;
SELECT *FROM #Tmp1;

It appears the ONLY time you get an error with a temporary table and an estimated execution plan is when you create the table with SELECT INTO.  In fact, with SELECT INTO, you'll get the error even if you create a permanent table in the batch.

And I learned this from questions that were asked of me … which is where I learn all kinds of really interesting information!

Happy Tuesday!

~Kalen

Published Tuesday, July 13, 2010 8:02 AM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Hi Kalen,

You forgot to include the logo :-)

July 13, 2010 2:41 PM
 

Kalen Delaney said:

oh, well, I guess I blew it; I missed that part of the rules. I have no idea how to include the logo so I guess I'll change the title so it isn't a TSQL Tuesday entry.

July 13, 2010 3:37 PM
 

Adam Machanic said:

Just insert an IMG tag in the editor in HTML mode. Or there's an icon that will do the same thing, but I personally like to do the editing by hand so I can control placement a bit better.

I thought you used Live Writer, though? You just drag in the logo.

July 13, 2010 6:52 PM
 

Rob Farley said:

Adam - maybe the host each month should post the HTML for it, linking to a publicly accessible version of the image for the src, and the href to their host-post. Just a thought...

July 14, 2010 2:40 AM
 

Adam Machanic said:

Great idea, Rob! Will put that into play starting next month :-)

July 14, 2010 10:15 AM
 

Paul Swinfield said:

Hello,  Often  if I see differences in the estimated and the actual plan its down to the indexes hitting their tipping points.  Thanks for the article.

July 17, 2010 1:00 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement