Understand Table Variable and Temp Table In SQL Server

Introduction 

As we know both Temp table and Table Variable are used to store the data temporarily.

But both have their pros and cons.We should have good understanding when to use what to improve our coding standard.

Lets understand the pros and cons and why we need over one another.

Get Started

Table variable is nothing but declaring a table like a variable declared in SQL Server variable .

Table variable name is prefixed with @ symbol.

DECLARE @MyTableVar TABLE
(
id INT,
Name VARCHAR(20)
)

Temp Table is nothing but creating a table like Table created in SQL Server , but for the current session(page).

Table variable name is prefixed with # symbol.

CREATE TABLE #MyTableVar

(

id INT,

Name VARCHAR(20)

)

Now both will give the same output . Lets see in action .

Execute the below code.

DECLARE @MyTableVar TABLE

(

id INT IDENTITY(1,1),

Name VARCHAR(20)

)

INSERT INTO @MyTableVar VALUES('A')

SELECT * FROM @MyTableVar

CREATE TABLE #MyTableVar

(

id INT IDENTITY(1,1),

Name VARCHAR(20)

)

INSERT INTO #MyTableVar VALUES('A')

SELECT * FROM #MyTableVar

DROP TABLE #MyTableVar

Output

Both are behaving same . 

Now a question comes to our mind why we need two types of table when we can achieve everything in a table?

Answer is we can not achieve everything in one types of table. Both have their pros and cons.

Difference between Table Variable and Temp Table

Temp table stored in Tempdb database where as Table variable stored in memory, but under memory pressure Table variable pushed to tempdb database.

1. DDL command will not applicable for Table Variable while we can apply DDL command for Temp Table.
2. Table variable drop immediately after the execution where as Temp table drop after the end of the session or with the drop command.
3. Temporary variable can not involve in Transaction,Logging and Locking while Temp variable Involve on this.
4. We can not create Index separately for Table Variable where as we can create for Temp table.
5. Table variable can be passed as a parameter to function and stored procedure where as we can not pass the temp table.
6. Temp table allows schema modification where table variable not.
7. Temporary variable is visible with in the scope where as temp table visible till the session end.

Share:

Read More