Nonshareable Temporary Tables

Nonshareable temporary tables (temp tables) have several uses, including debugging stored procedures and triggers.

Unlike standard tables, where data needs to persist beyond the existing session, use temporary tables when data is required only within a single process or session.

You define a temporary table by including a pound sign (#) at the beginning of the table's name. A temporary table is created and populated in the system database tempdb and includes a numerical identifier appended to the end that differentiates it from similarly-named #temp tables. For example, if you create the temporary table #test_temp, the actual table name might be #test_temp00002013927. The temporary table and data it contains is visible only in the current scope (stored procedure, set of nested stored procedures, or trigger). The table is dropped automatically when the session ends, or if you manually drop the table.

Note: Although the temp table is dropped automatically when the session in which it is created ends, it is more efficient to drop the table when you are finished using it.

Temporary tables allow for short term use of data, some examples include:

Tracking temporary user information – use a temporary table as a Web application's e-commerce shopping cart to collect purchases. If the transaction is canceled, the shopping cart (temporary table) is dropped when the session ends.
Code maintenance – cursors can have considerable overhead. Maintaining code may be easier if you use temporary table data with Transact-SQL commands (create a series of temporary tables and select data from them to get a final result).
Debugging stored procedures and triggers – it may be easier to debug your stored procedure by keeping the results of a called stored procedure (or multiple stored procedures) in a temporary table.