What are difference between the temp table and table variable in SQL Server?

Temporary Variables:

1) The temp table can be created using the #sign and table name.

— Create Temporary TableCREATE TABLE #Employee

(Id INT, Name VARCHAR(50))

–Insert Two rows

INSERT INTO #Employee

VALUES(1,’Ram’)

INSERT INTO # Employee

VALUES(2,’Karan’)

–Retrieve the records from temp table

SELECT * FROM #Employee

–DROP Temporary Table query

DROP TABLE #Employee

GO

2) Temp table support the DML operation. We can create, alter and drop temp tables.

3) Temporary table cannot be used in user defined functions.

4) Temporary tables are created into the tempdb database.

5) In Temporary table we can add explicit index.

Table Variables:

 

We can declaring a Table Variable, Inserting records into it and retrieving the rows from table variables.

— Create Table VariableDECLARE @Employee TABLE

(

Id INT,

Name VARCHAR(50)

)

–Insert Two records

INSERT INTO @Employee

VALUES(1,’Ram’)

INSERT INTO @Employee

VALUES(2,’Karan’)

–Retrieve the records from table variables

SELECT * FROM @Employee

GO

2) Table variables does not support the DML operations.

3) Table variables can be used in user defined functions.

4) Table variables are created in in memory.

5) In Table variables cannot support adding the explicit index.

Leave a Reply

Your email address will not be published. Required fields are marked *

9 + 9 =


All Rights Reserved 2019 | DesignzRush (OPC) Pvt. Ltd.
Inline
Inline