For example, if you have an automated job that requires data to be pulled from a view and that view requires 4 minutes to produce results, how long will the job take to complete if the view needs to be accessed 20 times before the job has been completed?
Temporary or Variable Table
Temporary and variable tables are very useful. Creating these types of tables on demand, loading the data from the view into the table, and dropping the table when it is no longer needed, can be done in a snap. The benefit here is that the view in our example will take 4 minutes to produce the results, but once the data is stored in the temporary table, you can access that data in milliseconds over and over again because the data in the temporary table is static, unlike a view which may have complex joins and sorts. Here is an example of how to create a temporary table. Temporary tables are quick. Since you are creating and deleting them on the fly, they are generally cached in memory. However, If you are using SQL Server 2000 or higher, you can also take advantage of variable tables. These are similar to temporary tables except offer more flexibility and stay in memory. In addition, they do not need to be dropped when you no longer need them. It is a good idea to use variable tables for small amounts of data. Use temporary tables for large data sets.
