Dynamic SQL and Temporary Tables in SQL Server

In SQL Server, creating and using Temp Tables using dynamic SQL is a good feature when we need to temporarily create tables at run time and delete automatically all within a session. They can be very useful when we need to store temporary data in a structured format and do data manipulation using Data Manipulation Language in SQL. In this article let us discuss Temp Tables or Temporary Tables and their uses with examples.

What is Temp Tables?

Temp Tables are Temporary Tables that are created using a '#' or '##' sign as a prefix to the table name. Temp Tables are generally used in dynamic SQL and are used for storing and manipulating data like using calculations or data concatenation or applying some filtering on the data. The scope of the local temp table is within the current session. But the scope of the global temp table is visible to all sessions and it will be deleted when the SQL Server re-starts or is physically deleted by the user.

Example:

#TempStudent

or

##TempStudent

Types of Temp Tables

When creating Temp Tables, the scope and lifetime of the temporary tables should be taken into consideration. So based on the scope and lifetime Temp Tables can be of two types, namely Local Temp Table and Global Temp Table.

Local Temp Table

The local Temp Table is created with single '#' sign as prefix to the table name.

They are visible only to the session that creates the Temp Table and it is automatically dropped when the session ends.

Example 1:

CREATE TABLE #tmpStudDemo (id INT,StudName varchar(100))
EXEC ('insert #tmpStudDemo values(101,''Bharath'')')
SELECT * FROM #tmpStudDemo

Explanation:

T-SQL code creates a local temporary table #tmpStudDemo, inserts a single row with values (101, 'Bharath') into the table using dynamic SQL, and then selects all rows from the table. Local temporary tables are session-specific, and they are automatically dropped when the session that created them ends.

Output:

Example 2:

Create Local Temp Table using a stored procedure:

Create Procedure TempTabDemo
(
@TTabName varchar(50),
@CodeNo int
)
As
Begin
EXEC (
'create table #' + @TTabName + ' (IdNo int)
insert #' + @TTabName + ' values(' + @CodeNo + ')
insert #' + @TTabName + ' values(' + @CodeNo + '+ 1 )
Select * from #' + @TTabName 
)
End

Execute the stored procedure 'TempTabDemo' created above with sample data, as below:

EXEC  TempTabDemo 'TempTableDem',101

Explanation:

This stored procedure dynamically creates a local temporary table, inserts rows into it based on the input parameter @CodeNo, selects data from the table, and performs these actions within a single session. The dynamic SQL allows for flexibility in generating table names and executing the necessary SQL statements.

Output:

Global Temp Table

The Global Temp Table is created using double '##' as prefix to the temporary table name. The global temp table is visible to all sessions. The global temp tables, once created will exist until the SQL Server is re-started or deleted by user. The global Temp Table can be useful in situations like, when we need to share temporary data across multiple sessions. Another important advantage of global Temp Table is that it can be created using dynamic SQL and used from outside of the dynamic SQL, where as Local Temp Tables created from inside a dynamic SQL is not visible outside of the dynamic SQL which created the temp table as its visibility is limited to the session of local SQL created it.

Example of Global Temp Table

We’re going to look at some examples of Global Temp Table to help you understand the topics better.

Exmaple 1:

DECLARE @SQLStatement NVARCHAR (1000);
SET @SQLStatement = 'CREATE TABLE ##MyTempTable (SNo INT,StudName varchar(100));';
EXEC sp_executesql @SQLStatement;
Insert into  ##MyTempTable values (101,'Rajesh')
SELECT * FROM ##MyTempTable;
Drop Table ##MyTempTable

Explanation:

This SQL code dynamically creates a global temporary table, inserts a row into it, selects all rows from it, and then drops the table. The use of global temporary tables allows for temporary storage of data that is visible across different sessions, and the table is automatically dropped when the session that created it ends.

Output:

Example 2:

Create Global Temp Table using a stored procedure:

Create Procedure TempTabGlobalDemo
(
@TTabName varchar(50),
@CodeNo int
)
As
Begin
EXEC (
'create table ##' + @TTabName + ' (IdNo int)
insert ##' + @TTabName + ' values(' + @CodeNo + ')
insert ##' + @TTabName + ' values(' + @CodeNo + '+ 1 )
insert ##' + @TTabName + ' values(' + @CodeNo + '+ 2 )
Select * from ##' + @TTabName
)
EXEC('Drop Table ##' + @TTabName )

End

Execute the stored procedure 'TempTabGlobalDemo' created above with sample data, as below:

EXEC  TempTabGlobalDemo 'GTempTableDem',101

Expalantion:

This stored procedure dynamically creates a global temporary table, inserts rows into it based on the input parameter @CodeNo, selects data from the table, and finally drops the table. The dynamic SQL allows for flexibility in generating table names and executing the necessary SQL statements.

Output:

Advantages of Using Temporary Tables with Dynamic SQL

The Temp Table in SQL Server provides many advantages over the regular SQL Server Database tables. Listed below are the advantages:

• Speed of Execution: While using the Temp Tables the execution of query can be faster as there is less logging and locking overheads for temporary tables.
  
• Access rights/security of Temp Tables: Temporary tables can be used for insert, update and delete operations on its data or on other tables without any security or access rights concerns.
  
• No Concurrency Issues: Using Temp Tables avoid any concurrency issues while Data insert, update or delete, as these temporary tables are only visible to the current session.
  
• Resource Efficient: As the Temp Tables are created and deleted from the same session, they take no memory space permanently and so it is more resource efficient.
  
• Better Query optimization: The Temp Tables provide better query optimization for data of any size.

Conclusion

In SQL Server creating Temp Tables are useful to save temporary data in the database and manipulate the same data. This will help to avoid memory issues as the Temp Tables are deleted at the end of the session if the Temp Table type is 'Local' and it can save memory usage. Since Temp Tables are created and used in dynamic SQL, care should be taken about any security issues. Whether dealing with intermediate storage or optimizing complex queries, temporary tables are a valuable tool in your SQL Server toolkit.