Well, I think everyone will agree that there are a few myths surrounding the use of Temporary Tables and Table Variables and this usually leads to confusion or preconceived notions when having to choose the appropriate type.
In this post, I will talk about Temp Tables and will share some basic features of Temp Tables before we move onto Table Variables and then we will do the comparison between the two.
So I will start from basics:
Temp Tables:
SQL Server has supported temporary table use for any years. Temporary tables are created and used just like any other tables, and then can be either local or global.
–Creating LOCAL temporary Table
CREATE TABLE #First_Temp_Table
(
RowId INT IDENTITY (1,1),
My_Name VARCHAR(100),
My_ADDRESS VARCHAR (100)
)
— Creating GLOBAL temporary Table
CREATE TABLE ##First_GlobalTemp_Table
(
RowId INT IDENTITY (1,1),
My_Name VARCHAR(100),
My_ADDRESS VARCHAR (100)
)
Local TEMP table objects always starts with ‘#’ whereas Global TEMP table objects starts with ‘##’.The global temp tables can be shared among the connections, while local tables are exclusively used by the connection that created them. In fact, SQL Server appends a unique string to the name of each local temp table in TempDB so that the local temp tables can’t be shared.
Suppose I create a temp table with the following statement:
CREATE TABLE #Second_Temp_Table
(
RowId INT IDENTITY (1,1),
My_Name VARCHAR(100),
My_ADDRESS VARCHAR (100)
)
If I check the sysobjects table in the TempDB I’ll find that SQL Server has appended a suffix to this table to make it unique for my connection:
USE TempDB
GO
SELECT name from sysobjects WHERE name LIKE ‘%#Second%’
Result:
#Second_Temp_Table______________________________000000003899
Features of TEMP Tables:
Scope: Within a connection, a temporary table object is visible to the creating level and inner levels (nested). For example, if you create a stored procedure and declare a temporary table object within it, you can call another stored procedure from that stored procedure (a nested stored procedure) and perform operations like inserting, updating and deleting that temporary table object. Once the main creating level terminates, the temp table is automatically destroyed.
Transaction Logs: Transaction Logs are recorded for Temporary Table and hence we can rollback transactions in the temporary tables.
Locking: The prospect of table locking is reduced when it comes to local temporary tables since this table is being used by only one user. One aspect where you might want to keep this in mind is that if you cancel a transaction which contains the creation of a temp table object and then cancel that query, an exclusive and update lock can appear on the tempdb. This lock will persist till the complete transaction has closed with a COMMIT or a ROLLBACK
Logging: There is less logging involved with temporary tables compared to permanent tables.
Indexing: We can create indexes on temporary tables explicitly on them. Hence, there is scope for performance enhancement when you talk about temp tables.
Constraints: All constraints are available for exploiting on a temp table EXCEPT when it comes to referring a FOREIGN KEY Constraint
Statistics: SQL Server can create STATISTICS for temp tables just like we do for permanent tables and therefore, the query optimizer has the option of choosing different plans. We need to always keep in mind that Stored procedures with temporary tables cannot be pre-compiled. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.
Temp Table Size: Can hold any volume of data. This will be a strong part of the deciding factor when you want to choose between a temporary table and a table variable.
I have tried to point out the main features of Temporary table and if I have missed any points then please raise it and we can discuss about it. In my next post , I will discuss about Table Variables. Till then Enjoy Coding!!!
Cheers
Subhro Saha