How To Create Temp Table On SQL Server

dbtut July 5, 2018 MSSQL

In SQL Server, tsql code developers may occasionally want to temporarily keep records on a table.

This can be done by creating a table in the database and using this table, but sql server provides global and local temp table for this job.

You can find the advantages and disadvantages of using temp tables in the table below.

Advantages	Disadvantages
There will not be a lock because it will only be a table for you and increases performance	Since temp tables will be kept in the tempdb database, performance problems will occur if the disks on which the tempdb database resides are slow.
If you are afraid to do it on a table in the production environment, you can safely do it on temp tables.	Tempdb will require extra disk space to grow the database (although small temp tables do not cause this problem, code developers can create large temp tables)
You can also do actions in a temp table (sort, filter, join)	With user defined functions, you can not insert, update and delete temp tables.
You can create indexes on temp tables.

Who created the table can only use this table over the same connection.

Local temp tables are deleted when the person creating this table disconnects from the instance.

You can create a local temp table, add a record, and select it as follows:

create table #TempTablo (Tempkolon varchar(50))
Insert Into #TempTablo VALUES('testkayıt')
Select * From #TempTablo

create table #TempTablo (Tempkolon varchar(50))

Insert Into #TempTablo VALUES('testkayıt')

Select * From #TempTablo

When the global temp table is created, it can be seen by all users and all connections.

Global temp tables are deleted when the people connected to this table disconnects from the instance.

You can create a global temp table, add a record, and select it as follows:

create table ##TempGlobalTablo (TempGlobalkolon varchar(50))
Insert Into ##TempGlobalTablo VALUES('testkayıt')
Select * From ##TempGlobalTablo

create table ##TempGlobalTablo (TempGlobalkolon varchar(50))

Insert Into ##TempGlobalTablo VALUES('testkayıt')

Select * From ##TempGlobalTablo

My next article, “What is Table Variables“, describes the use of Table Variables, which resemble temp tables.

The differences between these two concepts are often very curious.

That’s why you may want ro read the article named “Differences Between Temp Table and Table Variable“.

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux