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.