Main Difference b/w Temporarytable and Table variables

The Main difference between temporary tables and table variables are,

Temporary tables will not exist outside the Transactions where as table variables exist.

/* Difference b/w Temporary table and Table variable */
ALTER PROCEDURE Temp_table_test
AS
BEGIN
BEGIN TRANSACTION

CREATE TABLE #temp (
id INT ,
name VARCHAR (50))

DECLARE @tab TABLE(
id INT ,
name VARCHAR (50)
)

/* inserting into temporary table */
INSERT INTO #temp
SELECT 1,
‘test’
UNION ALL
SELECT 2,
‘test2′

/* inserting into temporary variable */
INSERT INTO @tab
SELECT 1,
‘test’
UNION ALL
SELECT 2,
‘test2′

ROLLBACK

SELECT *
FROM @tab
END

Result Set:

id name

1               test

2              test2

/* Difference b/w Temporary table and Table variable */
ALTER PROCEDURE Temp_table_test
AS
BEGIN
BEGIN TRANSACTION

CREATE TABLE #temp (
id INT ,
name VARCHAR(50))

DECLARE @tab TABLE(
id INT,
name VARCHAR(50)
)

/* inserting into temporary table */
INSERT INTO #temp
SELECT 1,
‘test’
UNION ALL
SELECT 2,
‘test2′

/* inserting into temporary variable */
INSERT INTO @tab
SELECT 1,
‘test’
UNION ALL
SELECT 2,
‘test2′

ROLLBACK

SELECT *
FROM #temp
END

Result :

Msg 208, Level 16, State 0, Procedure temp_table_test, Line 25

Invalid object name ‘#temp’.

Conclusion:

Temporary table will not exist outside a Transaction.

(2 row(s) affected)
(2 row(s) affected)
Msg 208, Level 16, State 0, Procedure temp_table_test, Line 25
Invalid object name ‘#temp’(2 row(s) affected)
(2 row(s) affected)
Msg 208, Level 16, State 0, Procedure temp_table_test, Line 25
Invalid object name ‘#temp’.