Difference between temp table and variable table

	Local Temp Table	Variable table
Syntax	Local temporary table name is stared with single hash ("#") sign. -- Create Temporary Table CREATE TABLE #TempEmp   (   ID INT,   Name VARCHAR(50)   ) --Insert Rows records INSERT INTO #TempEmp VALUES(1,'Bagesh Kumar Singh') INSERT INTO #TempEmp VALUES(2,'Rajesh kumar singh') --Retrieve the records SELECT * FROM #TempEmp	It is similar to variable and name is started with the @ sign. --Create variable table DECLARE @VarEmp TABLE (  ID INT,  Name VARCHAR(50)   ) --Insert Two records INSERT INTO @VarEmp VALUES(1,'Bagesh Kumar Singh') INSERT INTO @VarEmp VALUES(2,'Rajesh kumar singh') --Retrieve the records SELECT * FROM @VarEmp
Drop	Temp table need to drop DROP TABLE #TempEmp	No need to drop the variable table
DDL Modification	We can use DDL operation on temp table like create, Alter and drop. ALTER TABLE #TempEmp ADD EmpAdd VARCHAR(400) we can create indexes and statistics on temporary tables	We can’t perform DDL operation on variable table. we can’t create indexes and statistics on temporary tables
DML	We can perform DML manipulation on temp table update #TempEmp set Name='Bagesh kumar bagi' where ID=2	We can perform DML manipulation on variable table update @VarEmp set name='Bagesh kumar bagi' where ID=2
Storage Location	Temp table is store in TempDB	Variable table is stored in memory
User define function	We can’t use user define function in temp table	We can use user define function in variable table
Primary key	We can use primary key on temp table CREATE TABLE #TempEmp  (ID INT primary key,   Name VARCHAR(50)) When we inserting the duplicate value it will throw error	We can use primary key in variable table DECLARE @VarEmp TABLE (  ID INT primary key,  Name VARCHAR(50)  )