Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
I wrote some articles about Temp Table and Table Variable.
In general, people who are new to tsql code development are wondering which one to use will be more powerful.
So I wanted to write an article that points out the differences of these 2 concepts.
The articles I wrote earlier can be found on the links below.
“How To Create Temp Table On SQL Server“,
Below you will find the comparison table for Temp Tables and Table Variables
| Temp Tables | Table Variables |
| Syntax is different. Temp tables can be created as follows. create table #TempTablo (Tempkolon varchar(50)) | Syntax is different. You can create the Table Variable as follows. DECLARE @TableVariableTablo table(TableVariableKolon varchar(50)) |
| They are kept in tempdb and transactions on temp tables are logged in the transaction log of tempdb.
| In general, there is a misconception that they are held in memory. Table Variables are also kept in tempdb. You can test it with the following code. If you look at the tempdb’s size at certain intervals after running the code, you will see that it increases.
DECLARE @TableVariableTablo table(TableVariableKolon varchar(50)) while(1=1) BEGIN Insert Into @TableVariableTablo VALUES(‘testkayıt’) END |
| By creating an index, you can overcome the problem of performance.
| Prior to SQL Server 2014, indexes were created with Primary Key and Unique Constraints. Non unique index could not be created. With SQL Server 2014, the non unique index can be created as follows.
DECLARE @TableVariableTablo TABLE ( Kolon1 INT INDEX IX1 CLUSTERED, Kolon2 INT INDEX IX2 NONCLUSTERED, INDEX IX3 NONCLUSTERED(Kolon1,Kolon2) /*An index containing Kolon1 and Kolon2*/ ); |
| Temp tables work better than table variables in Large datasets. | When the execution plan is created, it is created assuming that there are 0 records on the table variable. That’s why there will be a performance problem on large datasets. |
| Temp Tables have Transaction structure. So you can rollback a transaction like this: create table #TempTablo (Tempkolon varchar(50))
BEGIN TRANSACTION Insert Into #TempTablo VALUES(‘testkayıt’) ROLLBACK
select * from #TempTablo | There is no transaction structure in Table Variable. You can not do this when you want to rollback a transaction like this:
DECLARE @TableVariableTablo table(TableVariableKolon varchar(50)) BEGIN TRANSACTION Insert Into @TableVariableTablo VALUES(‘testkayıt’) ROLLBACK
Select * From @TableVariableTablo |
| If there is a temp table in the stored procedure, the store procedure will be recompiled each time and a new execution plan will be created each time.
This will prevent one of the main purposes of the stored procedure, “avoiding the query plan every time, avoiding this cost”.
| If a table variable exists in the stored procedure, the recompile operation does not occur.
|
| You can create a temp table With SELECT INTO. | You can not create a table variable with SELECT INTO. |
| If you are using a global temp table, you may have a lock problem because other people can use this temp table and have a transactional structure. | Table Variables can not use the transaction structure. There is no lock problem at all. |
| DDL commands such as ALTER and DROP can be run after temp tables are created.
| DDL commands such as ALTER or DROP can not be executed on Table Variables after they are created. If you define a Table Variable in Sp or funtion, the table variable is automatically deleted when the sp or function ends. |
| temp table can not be created in user-defined function | A table variable can be created in a user-defined function. |
| Temp tables can be used in nested stored procures | Table Variables can not be used in nested stored procedures. |
