Temp Table – SQL

Introduction

SQL Server provides the concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside tempdb database.

Types

SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:

• Local Temp Table
• Global Temp Table

Local Temp Table

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

Global Temp Table

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

Alternative Approach: Table Variable

Alternative of Temporary table is the Table variable which can do all kinds of operations that we can perform in Temp table. Below is the syntax for using Table variable.

Declare @TempTableVariable TABLE(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

insert into @TempTableVariable values ( 1, 'Abhijit','India');

select * from @TempTableVariable

When to Use Table Variable Over Temp Table

Tablevariable is always useful for less data. If the result set returns a large number of records, we need to go for temp table.