TempDB is a shared resource for every database hosted on an instance of Microsoft SQL Server. TempDB is used as working storage to optimize query performance. One way you can see it used is for sorting, or organizing data. When you join two tables not having indexes on the data being joined, SQL Server will sometimes parse the data and create a hash table against which the joins will be performed. In this case, TempDB often stores the intermediate data for processing at a later time. When you have GROUP BY or ORDER BY clauses, TempDB is often used for that sorting and organizational purpose.
In a recent post David Eaton leaves a comment about TempDB and his preference to not intentionally use it. Just like the query analyzer users TempDB to break up work into more manageable pieces, you can write your queries to do the same thing. You have the option of using Inline Views, Common Table Expressions, Memory Variables, or Temporary Tables in TempDB. Inline Views and Common Table Expressions pretty much handle how they use TempDB on their own. They may never even hit TempDB depending on how they are used.
But Memory Tables and #Temp tables grant more capabilities to the developer. Not only can you specify exactly the schema, but you can also create constraints, and using # tables, you can even create indexes. David points out that if you are creating indexes to gain performance, then you may not really have a temporary table. Perhaps you need permanent tables in your database, separated on different file systems objects for isolation, where you can do your intermediate processing. Using the separate file system separates the load from your other tables. Creating them in the application database assures that the load is not impacting other databases by doing on the work in TempDB.
David prefers this implementation of using TempDB, saying it balances the load such that a single database with a lot of “Temporary” work has less impact on other databases hosted on the same instance of SQL Server, because they do not use a shared resource, TempDB, for work that the developer controls.
This doesn’t mean that a database won’t use TempDB. It simply allows you to take heavy lifting kinds of work and offload it from TempDB. I use this same technique a lot when I have batch work to do. I will often have staging tables that I will purge and re-populate with a new batch. In fact, I often drop indexes before adding new data, and then re-create the indexes after the data has been imported. Guess what, creating the indexes can use TempDB, depending on the situation.
The primary reason I like using #temp tables in TempDB is the automated isolation of the scope for the table. If I use a permanent table in my database I have to isolate the records for my work, or allow my application to create and drop tables. Using #Temp tables in a stored procedure allows my code to always reference the table with a specific name, and SQL Server handles the separation of instances from one process to another.
Do you use working structure in your application databases? How have you found them to be useful? Leave a comment with your experience.
Cheers,
Ben