T-SQL programming often involves the need to materialize data temporarily. Temporary tables are just one solution; other ways for handling an independent physical or logical materialization of a set include table variables and table expressions such as views, inline user-defined functions (UDFs), derived tables, and common table expressions (CTEs).
You might need to physically persist interim states of your data for performance reasons, or just as a staging area. Examples of such scenarios include:
Materializing aggregated data to some level of granularity (for example, employee and month), and issuing running, sliding, and other statistical reports against that data
Materializing a result of a query for paging purposes
Materializing result sets of interim queries, and querying the materialized data
Materializing the result of a CUBE/ROLLUP query, and issuing queries against that data
Walking through the output of a cursor and saving information you read or calculate per row for further manipulation
Pivoting data from an Open Schema environment to a more traditional form, and issuing queries against the pivoted data
Creating a result set that contains a hierarchy with additional attributes such as materialized paths or levels, and issuing reports against the result
Hold data that needs to be scrubbed before it can be inserted
One of the benefits of materializing data in a temporary table is that it can be more compact than the base data, with preprocessed calculations, and you can index it when it might be inefficient or impractical to index all the base data. Performancewise, you benefit from materializing the data when you need to access it multiple times, but in some cases, even when all you have is a single query against the data, you benefit.
You might also need to materialize interim sets logically in virtual temporary tables (table expressions) to develop solutions in a modular approach. I’ll show examples in this chapter that address this need as well. Either way, there are many cases in which using temporary tables, table variables, or table expressions makes sense.
There’s a lot of confusion around choosing the appropriate type of temporary object for a given task, and there are many myths regarding the differences between temporary tables and table variables. Furthermore, temporary tables and table variables are often abused because of lack of knowledge of efficient set-based programming. This is one area in which programming maturity comes into play, just as maturity helps you decide between using cursors or set-based solutions and between using dynamic execution or static code.
In this chapter, I will try to provide you with a clear picture of how the different temporary object types behave, in which circumstances you should use each, and whether you should use them at all. At the end of the chapter, I’ll provide a summary table (Table 2-4) that contrasts and compares the different types. This table will cover the factors you should take into consideration before making your choice.