Temporary Tables vs. Table Variables

Here is an interesting behavior with SQL Server. A lot of developers use table variables and temporary tables interchangeably without understanding that they are different entities, each with slightly different behaviors. Here is one of the more interesting differences

Transaction Rollback

If you have a transaction that affects a temporary table, and roll it back, the temporary table is restored to it's original state. For example

SET NOCOUNT ON
CREATE TABLE #TempTable (TempTableColumn int)
DECLARE @TableVar TABLE (TableVarColumn int)
BEGIN TRANSACTION
INSERT INTO #TempTable SELECT 1
INSERT INTO @TableVar SELECT 1
ROLLBACK TRANSACTION
SELECT * FROM #TempTable
SELECT * FROM @TableVar

Results in

TempTableColumn
---------------
TableVarColumn
--------------
1

As you can see the temporary table was rolled back but the table variable was not.

This has several possible uses. An example I have used is, when I wanted to rollback a transaction, I copied the relevant records that were changed previously to a table variable and then rolled back the transaction. That way I had a table available with the old data after the rollback.