Now that you’ve learned about table variables and temporary tables, you might want to consider when you’d use them within a script and when you’d create a new standard table or view or simply use a derived table instead. Table below presents a comparison of these five types of table objects. Note that although a view isn’t technically a table, I’ve included it below because it can be used in place of a table.
One of the biggest differences between these objects is their scope, which determines where it can be used in a script. Because standard tables and views are stored permanently within a database, they have the broadest scope and can be used anywhere, including in other scripts on the current connection or other scripts on other connections. In contrast, a derived table exists only while the query that creates it is executing. Because of that, a derived table can’t be referred to from outside the query. As you’ve just learned, temporary tables and table variables fall somewhere in between.
Another difference between the five table types is where they’re stored. Like standard tables, temporary tables are stored on disk. In contrast, table variables and derived tables are stored in memory if they’re relatively small. Because of that, table variables and derived tables usually take less time to create and access than standard or temporary tables.
Although a view is also stored on disk, it can be faster to use than any of the other table objects. That’s because it’s simply a precompiled query, so it takes less time to create and access than an actual table. However, with the other table objects, you can insert, update, or delete data without affecting any of the base tables in your database, which isn’t true of a view. For this reason, you can’t use a view in the same way as the other table objects. But if you find that you’re creating a table object that doesn’t need to be modified within your script, then you should be defining it as a view instead.
In most scripts, table variables and temporary tables can be used inter-changeably. Since a script that uses a table variable will outperform the same script with a temporary table, you should use table variables whenever possible. However, table variables are dropped when the batch finishes execution. So if you need to use the table in other batches, you’ll need to use a temporary table instead.
The five types of Transact-SQL table objects
| Type | Scope |
|---|---|
| Standard table | Available within the system until explicitly deleted. |
| temporary Table | Available within the system while the current database session is open. |
| Table Variable | Available within a script while the current batch is executing. |
| Derived Table | Available within a statement while the current statement is executing. |
| View | Available within the system until explicitly deleted. |
Description
- Within a Transact-SQL script, you often need to work with table objects other than the base tables in your database.
- The scope of a table object determines what code has access to that table.
- Standard tables and views are stored permanently on disk until they are explicitly deleted, so they have the broadest scope and are therefore always available for use.
- Derived tables and table variables are generally stored in memory, so they can provide the best performance. In contrast, standard tables and temporary tables are always stored on disk and therefore provide slower performance.
- To improve the performance of your scripts, use a derived table instead of creating a table variable. However, if you need to use the table in other batches, create a temporary table.
- Finally, if the data needs to be available to other connections to the database, create a standard table or, if possible, a view.
- Although a view isn’t a table, it can be used like one. Views provide fast perfor-mance since they’re predefined, and high availability since they’re permanent objects. For these reasons, you should try to use a view rather than create a table whenever that’s possible. However, if you need to insert, delete, or update the data in the table object without affecting the base tables of your database, then you can’t use a view.
- A common table expression (CTE) is a type of derived table. For more information about CTEs, see chapter “How to code Subqueries“.
