Difference between temporary table and table variable in sql server

1. Scope of temporary variable is session. For example if we will execute following query in same tab of sql server it will execute fine.

CREATE TABLE #temp(
ID INT,
Name VARCHAR(50)
)

Go
INSERT INTO #temp VALUES(1,'Davis')
SELECT * FROM #temp

But if we will execute following query in other tab

SELECT * FROM #temp

We will get error message like: Invalid object name '#temp'.

Note: When we open new query page or tab in sql server management studio it create a new session.

Scope of table variable is batch. For example we will execute following sql queries

DECLARE @table TABLE(
ID INT,
Name VARCHAR(50)
)

Go
INSERT INTO @table VALUES(1,'Davis')
SELECT * FROM @table

We get error like: Must declare the table variable "@table"

Note: Go keyword in sql server always creates a new batch.

2. Temporary table is same as normal table with different scope. We can create non – clustered index on temporary table. For example:

CREATE TABLE #temp(
ID INT,
Name VARCHAR(50)
)
CREATE NONCLUSTERED INDEX temp_table_index
ON #temp(ID , Name)

We cannot create a non-clustered index on table variable. For example:

DECLARE @table TABLE(
ID INT,
Name VARCHAR(50)
)
CREATE NONCLUSTERED INDEX temp_table_index
ON @table(ID , Name)

We will get some error message.

Note: If we have large amount of data and performing quires on it then temporary table may be better option than table variable since we can increase the performance of a query by creating indexes on it.

3. There is not any table lock concept in the table variables. For example it is correct to write:

SELECT * FROM #temp WITH(NOLOCK)

While it is incorrect to write:

SELECT * FROM @table WITH(NOLOCK)

3. Unlike to the temporary table, table variables also not support transaction and logging. For example:

CREATE TABLE #temp(
ID INT,
Name VARCHAR(50)
)

BEGIN TRANSACTION
INSERT INTO #temp VALUES(1,'Davis')
ROLLBACK
SELECT * FROM #temp

Select query will not return any rows since it has been rollback while if will perform same operation on table variable for example:

DECLARE @table AS TABLE(
ID INT,
Name VARCHAR(50)
)

BEGIN TRANSACTION
INSERT INTO @table VALUES(1,'Davis')
ROLLBACK
SELECT * FROM @table

In this case select query will return one row like
ID Name
1 Davis

Since table variables doesn’t support transaction and rows has not been roll backed.
4. Tables variables in the stored procedures are pre- compiled while temporary tables are not.
5. We can pass the table variables as a parameter of stored procedures or functions. For example:

CREATE TYPE InfoType AS TABLE(
ID INT,
Name VARCHAR(50)
)

GO

CREATE PROCEDURE GetInfo(
@table AS InfoType READONLY
)
AS
BEGIN
SELECT * FROM @table
END


Now we will execute the above stored procedure by passing table variable.

DEClARE @info AS InfoType
INSERT INTO @info VALUES(1,'Davis')

EXECUTE GetInfo @info

Output:
ID Name
1 Davis


While we cannot pass the temporary table. For example:

CREATE TABLE #temp(
ID INT,
Name VARCHAR(50)
)

INSERT INTO #temp VALUES(1,'Davis')
EXECUTE GetInfo #temp

We will get the error message like: nvarchar is incompatible with InfoType
6. A table value function can return table variable but it cannot return temporary table.
7. With help of SELECT INTO we can directly create the temporary table while it is not possible for temporary variables. For example:

SELECT * INTO #temp
FROM Student

While it is not possible in case of table variables. For example

SELECT * INTO @table
FROM Student

If we will execute above query we will get error message like: Incorrect syntax near '@table'

Posted in: