As you probably know, one of the bigger features added in SQL Server 2014 is the In-Memory OLTP engine, with its support for memory optimized tables, hash and BW-Tree indexes and natively compiled stored procedures.
Variable assignments, just like column assignments, can be made using the full range of T-SQL expressions that you could apply to regular columns in an UPDATE, allowing you to also write more complex expressions.
On the other hand, temporary tables are only available to the current connection to the database for the current user. Whenever there is a rollback of a transaction, it does not affect the table variables.
As we can see in the output of the query below – even after a rollback, we are still seeing data in table variable whereas data in temporary table is cleaned up.
Examples of such scenarios include: One of the benefits of materializing data in a temporary table is that it can be more compact than the base data, with preprocessed calculations, and you can index it when it might be inefficient or impractical to index all the base data.
In terms of performance, you typically benefit from materializing the data when you need to access it multiple times, but in some cases, even when all you have is a single query against the data, you benefit.