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?