Updating table variables
You can use a table variable for just about anything you'd use a regular table. A table variable goes out of scope immediate after the batch ends -- just like regular variables.
You create a table variable the same way you create any other variable: using the declare statement: This declares a table variable named @Table Var that we can use in place of a temporary table. When you create a table variable it only resides in memory which means it's much faster.
It’s also important to note how table variables do not participate in transaction rollbacks.
Although this can be a performance benefit, it can also catch you off guard if you are not aware of the behavior.
Knowing what we have learned so far, how do you decide on using a table variable or a temporary table?
First, we know there are situations that which demand the use of a temporary table.
Also because they aren't physically written to the disk they aren't impacted by transaction rollbacks. As I make changes to the code I'm gradually replacing all the temporary table with table variables without any problems.
Just using the word "Wallops" is haf the reason your question got answered! If you're using temporary tables and don't need transactions on those tables and want better performance I'd strongly consider using table variables instead.Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries.