Wednesday, July 11, 2012

Strange Behavior for Table Variable with Indexes

I was trying some research on table variable with indexes and came across with this strange behavior which I can’t explain. If you can let me you are welcome.

Let me clear the cache first so we are clear.


So I created table variable with unique clustered index and insert data into the table variable.


So let us see the query plan.


So it uses, clustered index of the SalesOrderDetail table and the clustered index of the table variable.

Let us view the cashed plans.


So we have two rows.

First one of course, plan for the table variable insert and the second one (This is what I can’t understand) is for Sales.vOrders indexes view.

I am not using any Index view. Other thing is note here is,  if I don’t have the indexes on the table variable. then this cached plan will not appear. Also, I do the simple select on the query which I am using to insert into table variable, then again this cached plan will not appear.

I don’t have a clue do you?


  1. I can't find a version of AdventureWorks that contains the indexed view, but it looks like the optimizer has chosen to use the indexed view to fill the query because it has decided it is the fastest way to do so. My guess would be that the index on the view matches the index on the table variable so the optimizer doesn't have to do a sort.

  2. Posting the following for Hugo as he doesn't have an account to login as with any of the listed services:

    First, this issue will not repro on a standard AdventureWorks database. You first have to run the code from the Books Online article about indexed views ( My guess is that you have done this at one time and never cleaned up. Also, if my theory below is correct, this will only repro on Enterprise Edition (or on Developer Edition, which is feature-equal to Enterprise Edition).

    Second, in spite of your observations in the last paragraph, this is neither related to the table variable, nor to its indexes. The code below will also reproduce this same behaviour:
    USE AdventureWorks2012;
    SELECT TOP(1) SalesOrderID, SalesOrderDetailID, ProductID
    FROM Sales.SalesOrderDetail
    ORDER BY SalesOrderID;

    To see what is really happening, you need to add two columns to the query that reads the plan cache: cacheobjtype and objtype. This will show that the second row (with the CREATE VIEW plan) is not an actual compiled plan, but the "parse tree" of a view. I was unable to find good links that describe this "parse tree" cache object type, but after running some tests I found two circumstances that cause these to appear:

    1. If a query references a view, SQL Server has to find its definition before it can compile an execution plan for the query. This causes parse tree entries to appear in the plan cache. But this is not the case in the situation above.

    2. If the optimizer, during plan compilation, considers the use of an indexed view, it also has to find its definition, again leaving a parse tree entry in the plan cache. That's what is happening here.

    So why did you not get the extra entry when you had no index on the table variable, or when you tried only the simple select query? That has to do with how the optimizer works. It is a so-called "cost-based optimizer" - which means it will first consider the most basic plans, and then only move on to more complex plans (in various steps) if the estimated cost is above some threshold.
    With no index on @t, or with only the query, the order of the rows is unimportant. In this case, the optimizer will pick a scan of the nonclustered index on ProductID in one of the first phases, and that lowers the estimated cost so far that the next phases can be skipped. With the index on @t (or with the ORDER BY in my repro above), scanning that index becomes more expensive (because an extra sort is required), so the optimizer picks a scan of the clustered index instead. But that is more expensive, so now the optimizer will move into the next phase, where indexed views are considered. And that causes the parse tree entry to appear.

    I hope any of this makes any sense to you....

    Hugo Kornelis

  3. The parse tree is brought into cache when cost-based optimization looks to match an indexed view with a logical GET operation (the rule is MatchGet in this case, more generally the Match* rules). Indexed view matching is only performed during cost-based optimization, so queries that qualify for a TRIVIAL plan will not perform IV matching.

    Things like adding ORDER BY or making the table variable a clustered table instead of a heap introduce plan choices, so a TRIVIAL plan cannot be used, and if the IV-matching logic runs as part of cost-based optimization, a parse tree is cached as part of that work.

    In SQL Server 2012, a parse tree is not loaded for the sample query because the IV could not possibly match the query's needs. In R2, the tree is loaded because the IV references the same table as specified by the logical GET - a bit pointless since it does not project the needed columns. One more reason to upgrade, I suppose. Another small improvement: the 2012 plan does not include the rowcount top seen in the blog screenshot, and the plan is not cached unless the query is actually executed (before 2012, e.g. obtaining an estimated plan was enough).

    Paul White

  4. Correction to my previous comment. I was using DBCC FREEPROCCACHE instead of the DBCC FREESYSTEMCACHE('ALL') in the image above. The latter command also clears the View Definition Cache, which DBCC FREEPROCCACHE does not do. So, the change in behaviour I saw between R2 and 2012 turns out not to exist, it was just a consequence of this error. It seems SQL Server caches a parse tree at the same time it caches the view definition, though the parse tree is not actually used by the optimizer in the case of the test query.