Accounts And Transactions
SAMPLE SELECT SQL- USING TEMPORARY TABLES
Temporary table at session level (one #) and Global level (two ##)
A session-level temporary table (#) only exists as long as the session in question exists. This temporary table remains accessible on the database server only at the level of this session in question for the user who created it.
A temporary global table (##) only exists as long as the session in question exists. This temporary table remains accessible on the database server for all users who have access to the server as long as the session in question exists.
A common technique for using temporary tables is in the scenario of generating large reports, or in the scenario of Data Mining
Temporary tables are created in tempdb. The name "temporary" is slightly misleading, for even though the tables are instantiated in tempdb, they are backed by physical disk and are even logged into the transaction log. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements. If created inside a stored procedure they are destroyed upon completion of the stored procedure. Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user. Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another - they would remain autonomous transactions and the tables would remain autonomous objects.
IMPORTANT:
There is also the possibility to use temporary table variables, however, we will not explore this theme in this example component at this time.
SAMPLE USING TEMPORARY TABLE SESSION LEVEL
SAMPLE USING TEMPORARY TABLE GLOBAL LEVEL