MYSQL SQL SERVER

SQL- What’s the difference between a temp table and table variable in SQL Server

Temporary Tables

Temporary Tables are a great feature that lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables. The temporary tables could be very useful in some cases to keep temporary data.

Types

There are mainly two types of Temporary Tables-Local & Global Temporary Tables.

1. Local Temporary Table:

These tables are only available for the session that has created them. Once the session is terminated, these tables are automatically deleted. They can be also be deleted explicitly.

2. Global Temporary Table:

These tables are available for all the sessions and users. They are not deleted until the last session using them is terminated. Similar to local Temporary Table, a user can delete them explicitly.

Syntax

The syntax for creating Temporary Table.

Sql Code
-- Create Temporary Table
	CREATE TABLE #Employee
	(Id INT, Name VARCHAR(50))
--Insert Two records
	INSERT INTO #Employee
	VALUES(1,'Max')
	INSERT INTO #Customer
	VALUES(2,'Clark')
--Retrieve the records
	SELECT * FROM #Employee
--DROP Temporary Table
	DROP TABLE #Employee
	GO

Table Variables

Scope. Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter. In fact, a table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement.
They can be declared in batch or stored procedure. Unlike Temporary Tables, they cannot be dropped explicitly. Once the batch execution is finished, the Table Variables are dropped automatically.

Tamp Table and table variable
Temp Table and table variable

Syntax

The syntax for Table Variable.

Sql Code
   -- Create Table Variable
	[email protected] TABLE
	(
	Id INT,
	Name VARCHAR(50)  	)
       --Insert Two records
	INSERT [email protected]
	VALUES(1,'Max')
	INSERT [email protected]
	VALUES(2,'Clarks')
      --Retrieve the records
	SELECT* [email protected]
	GO

Differences Between Temp tables and Table Variables in SQL Server

See also  How to install check_mysql_health plugin in nagios

In this section, we have listed the major differences between Temporary Tables and Table Variables. They are

Terms Temporary Table Table Variable
Storage Location The temporary tables are stored in tempdb database of SQL server. The Table Variables are stored in both the memory and the disk in the tempdb database.
User Defined Functions They are not allowed in the user-defined functions. The table variables can be used in user-defined functions.
Transactions They support the explicit transactions that are defined by the user. They do not participate in the transactions that have been explicitly defined by the user.
Indexes Local and Global Temporary Tables support creation of indexes on them in order to increase the performance. Table Variables do not allow creation of indexes on them.
Locking Since the Temporary Tables are physical tables, while reading from the table, SQL Optimizer puts a read lock on the table. Since the Table Variables are partially stored in the memory, they cannot be accessed by any other user or process that the current user. Therefore, no read lock is put on the Table Variable.

About the author

author

Wikitechy Founder, Author, International Speaker, and Job Consultant. My role as the CEO of Wikitechy, I help businesses build their next generation digital platforms and help with their product innovation and growth strategy. I'm a frequent speaker at tech conferences and events.

Add Comment

Click here to post a comment

X