Temporary Tables in SQL Server: Definition, Types and Exmaples | SQL Temp Tables

SQL Server Temporary tables

Types of temporary tables

• Local temporary tables : These tables are only visible within the current session and are dropped automatically when the session is closed. They are created using a single # symbol before the table name.

Syntax

• Global temporary tables : Global temporary tables are visible to all sessions and are dropped automatically when the last session using the table is closed. They are created using a double ## symbol before the table name.

Syntax

Difference between local and global temporary table

• Scope : Local temporary tables are only visible within the current session, while global temporary tables are visible to all sessions.
• Naming convention : Local temporary tables are prefixed with a single hash (#) symbol, while global temporary tables are prefixed with a double hash (##) symbol.

• Lifetime : Local temporary tables are dropped automatically when the session that created them ends, while global temporary tables are dropped when the last session that referenced them ends.
• Usage : Local temporary tables are typically used to store data that is specific to a single user or session, while global temporary tables are used to store data that needs to be shared across multiple sessions.
• Performance : Because global temporary tables need to be accessed and modified by multiple sessions, they can sometimes be slower than local temporary tables. However, this performance difference may be negligible in most cases.

EXAMPLES :

Creating local temp_table

Inserting values into local temp_table

Result :

Updating local temp table:

Result :

Updating Global temp table

Result :

Drop table : It drops the table from the database. DROP TABLE ##globaltemp_table Limitations of temporary tables While temporary tables are a useful tool for managing data in your database, there are some limitations and potential drawbacks. 

  Limited Storage Capacity : One of the main limitations of temporary tables is their limited storage capacity. Since temporary tables are stored in memory, they may not be able to accommodate large amounts of data. This can be especially problematic if you’re working with large datasets, as it may be necessary to use a permanent table or other storage solution. 

Database Management System Compatibility : Not all database management systems support temporary tables. While many of the most popular database management systems, such as MySQL and PostgreSQL, do support temporary tables, other systems may not. This can be a problem if you’re working with a less common database management system or if you need to transfer your code to a different system. 

Access Permissions : Depending on your database management system and the permissions you’ve been granted, you may not be able to create, modify, or delete temporary tables. This can be a problem if you need to use temporary tables as part of your data management strategy. 

Data Durability : Since temporary tables are stored in memory, they are not as durable as permanent tables. If your database management system crashes or is shut down, any data stored in temporary tables will be lost. This can be a problem if you need to ensure that your data is backed up or recoverable.

  Performance Considerations : While temporary tables can be a powerful tool for managing data, they can also have an impact on query performance. In some cases, it may be more efficient to use other data management strategies, such as subqueries or views, instead of temporary tables.
TIPS AND BEST PRACTICES FOR OPTIMISING THEIR PERFORMANCE

1. Use Temporary Tables Only When Necessary : Before creating a temporary table, consider whether it is really necessary. In some cases, a subquery or a view may be a better option. Avoid creating temporary tables for small or simple tasks that can be accomplished with other database operations.
2. Minimise Data Storage : Since temporary tables are stored in memory, it’s important to minimise the amount of data that is stored in them. This can be accomplished by selecting only the necessary columns, filtering rows based on specific criteria, and avoiding unnecessary joins or operations.
3. Use Simple Data Types : When creating temporary tables, use simple data types whenever possible. Complex data types can slow down query performance and consume more memory.
4. Use Appropriate Constraints : Constraints can help ensure data integrity and improve query performance. Use appropriate constraints, such as primary keys and indexes, to optimise query performance and prevent data inconsistencies.
5. Avoid Complex Operations : Complex operations, such as nested queries or complex joins, can slow down query performance and increase memory usage. Avoid complex operations whenever possible and break down complex queries into simpler components.
6. Use Indexes : Indexes can improve query performance by allowing the database to quickly locate specific rows in the temporary table. Use appropriate indexes on columns that are frequently used in queries.
7. Clean Up Temporary Tables : Since temporary tables are not automatically deleted, it’s important to clean them up when you’re finished using them. This can be accomplished by dropping the temporary table or by using a session-specific naming convention that ensures that temporary tables are not reused across multiple sessions.