Temp Tables and Table Vars

There are two types of temporary storage: temporary tables and table variables. Within temporary tables, there are two subtypes - local and global. For each you need to pay attention to how long the temporary objects last and who can see them (their scope).

Local temp tables have a name that starts with the pound sign. The session that creates them can see and use them. This is true for all levels within the session, such as a call to a procedure or function. Internally, SQL Server adds a suffix to the object name so there is no contention with similarly named objects in other sessions.

Global temp tables have a name that starts with two pound signs. They are visible to all session (thus global). There are destroyed when the original session that created them ends, and there are no other references to the objects.

Table variables are declared. You do not create them. Their name has an @ sign at the beginning. Only the batch that creates them can see them. They get destroyed at the end of the batch. They are not visible at lower levels, such as within a procedure or function call. Unlike temporary tables which get changed as part of a transaction, table variable changes are immediately and permanently applied.