How Can My Application Benefit from Temporary Tables

How can my application benefit from temporary tables?

Temporary tables are often valuable when you have a fairly complicated SELECT you want to perform and then perform a bunch of queries on that...

You can do something like:

CREATE TEMPORARY TABLE myTopCustomers
   SELECT customers.*,count(*) num from customers join purchases using(customerID)
   join items using(itemID) GROUP BY customers.ID HAVING num > 10;

And then do a bunch of queries against myTopCustomers without having to do the joins to purchases and items on each query. Then when your application no longer needs the database handle, no cleanup needs to be done.

Almost always you'll see temporary tables used for derived tables that were expensive to create.

What are the advantages of using a view over a temporary table in SQL Server

To discern, ask yourself if you need to reuse the information:

• a view is a glorified SELECT and it's used mainly for convenience
• you can materizalize a view, i.e. store it as a table and even index it. See this question
• use a temp table if you will not reuse the structure many times, like in a script that runs every now and then
• views will take space (especially if materialized) and having many views is difficult to maintain

Also note how temp tables are destroyed:

• if you create a temp table #tbl, it will be destroyed when it goes out of scope (e.g. at the end of the script).
• you can however create a temp table like ##tbl (with two #) and it will be destroyed when the connection ends.

What would be a better approach a temp table or ordinary table?

While communication with a temp table is technically an 'interdatabase' communication, tempdb is usually highly cached (or can be configured as such), so it's more likely to be a read from memory, then it is a read from disk.

By default, temp tables are also only accessible by the process that created it, which protects the data from being read by unauthorized users. Cleanup of the tables is managed automatically as well. It can also help reduce contention for resources in the working database.

From a practical standpoint, a regular database can be finely tuned to offer better performance than a #temp table, but for most purposes, a #temp table is going to be a more practical solution.

Tempdb overview:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31654.1550/html/sag1/X73131.htm

Tempdb Performance and Tuning:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00841.1502/html/phys_tune/X94507.htm

Which are more performant, CTE or temporary tables?

I'd say they are different concepts but not too different to say "chalk and cheese".

• A temp table is good for re-use or to perform multiple processing passes on a set of data.

• A CTE can be used either to recurse or to simply improved readability.
  
  And, like a view or inline table valued function can also be treated like a macro to be expanded in the main query

• A temp table is another table with some rules around scope

I have stored procs where I use both (and table variables too)

---

Related Topics