What are the best practices for using temp tables in recursive queries?

1 Scope of temp tables

Temp tables can have different scopes depending on how they are created and accessed. There are two main types of temp tables: local and global. Local temp tables are prefixed with a single hash sign (#) and are visible only to the current session. Global temp tables are prefixed with two hash signs (##) and are visible to all sessions. For recursive queries, it is usually preferable to use local temp tables, as they have less overhead, less contention, and less risk of interference from other sessions. However, if you need to share the results of a recursive query across multiple sessions, you can use global temp tables with caution.

3 Indexing of temp tables

Temp tables can benefit from indexes, just like regular tables, to speed up the queries that access them. However, creating and maintaining indexes on temp tables also has some costs, such as disk space, memory, and CPU usage. Therefore, it is important to balance the benefits and costs of indexing your temp tables and only create indexes that are necessary and useful for your recursive queries. When deciding which indexes to create, consider factors such as the size and cardinality of your temp tables, the frequency and type of your queries, as well as the timing and method of your index creation. For example, small or low distinct value tables may not need indexes at all due to SQL Server's ability to scan them efficiently. On the other hand, large or high distinct value tables may benefit from indexes to reduce scan operations and logical reads. Additionally, frequent or complex queries may require indexes to improve query performance and execution plans. Furthermore, you can create indexes either before or after inserting data and either explicitly or implicitly; creating them before inserting data can save time and resources. Lastly, creating indexes explicitly provides more control over options and properties but creating them implicitly can be simpler and faster.

4 Error handling and cleanup of temp tables

Temp tables are automatically dropped when the session or the batch that created them ends, unless they are global temp tables, which are dropped when the last session that references them ends. However, sometimes you may encounter errors or exceptions that can interrupt the normal execution of your recursive queries and leave your temp tables in an inconsistent or unwanted state. To handle these situations, you should use proper error handling techniques, such as TRY...CATCH blocks, error messages, and transaction control, to catch and handle the errors gracefully and to rollback any changes that may have affected your temp tables. You should also use explicit DROP TABLE statements to clean up your temp tables when you are done with them, especially if they are global temp tables, to free up the resources and avoid potential conflicts or leaks.

5 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?