Temp Tables Vs Table Variables

I have posted couple of blog post on the above topic which are very popular.

Those blog posts were,

Temp Tables Vs Table Variables Vs CTE

What's the difference between a temp table and table variable in SQL Server-

Today I came across with nice picture which explains every thing from http://sqlserverplanet.com

Isn’t this very simple and easy to understand?

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?

Collation Conflict 2

I posted a blog post about Collation Conflict some time back. With the discussions on Temp tables and table variables are on let us do this with a table variables.

Let us prepare the data set needed.

Then run the following using a table variable instead of temp table.

and this it is a success ( With using temp table, this will fail since tempDB in different collation)

So another place to use table variable and the debut continues …