News & tips
There are four types of tables in MS SQL:
A local temporary table, #table_name, exists only for the duration of the user session or the procedure that created the temporary table. When the user logs off or when the procedure that created the table completes, the local temporary table is lost. Multiple users can’t share a local temporary table because it is local to one user session.
A global temporary table, ##table_name, also exists for the duration of the user session or the procedure that created the table. The difference is that multiple users or sessions can access the global temporary table. When the last user session that references the table disconnects, the global temporary table is lost.
Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when no longer in use.
Full details of how to work with all these tables are covered in our SQL courses.
The syntax given below is used to create a local temp table in SQL Server:
The above script will create a temporary table in tempdb database. We can insert or delete records in the temporary table similar to a permanent table, for example:
And select records from that temporary table:
After execution of all these statements, if you close the query window and again execute “Insert” or “Select” command, you will see an error similar to:
This is because the scope of local temp table is bounded with the current connection of current user.
Below is the syntax for creating a global temporary table:
The above script will create a temporary table in tempdb database. You can insert, update or delete records in the global temporary table similar to a permanent table, or local temporary table.
Local and global temporary tables are stored inside the Temporary Tables folder of the tempdb database.
If you look closely look at the name of the temp tables, you will see a ‘dash’ and ID as part of the name:
SQL server does this automatically to be able to differentiate between the different user sessions; you can ignore this and refer to the table name only.
This example creates two temporary tables from an existing permanent table called WebContacts. The permanent table is a list of people and includes a column for their occupation and another column for their Country. The example calculates the percent of people in each country for each occupation.
Step 1. Create a temporary table named OccupationCount and at the same time populate it with summary data from the existing table:
Step 2. Create another temporary table named CountryCount which lists the number of people in each country for each occupation.
Step 3. Use the 2 temporary tables to list the percent of people in each country for each occupation:
Note: You can use Format to display the percentage symbol and you need to cast the count as a decimal to see the decimal places.
With Microsoft SQL Server, you can also create Table Variables, which can perform the same kind of operations that are performed with temp tables. Note: There can be a big performance difference between using table variables and temporary tables. In most cases, temporary tables are faster than table variables. Use table variables when you have just a few rows of data. Table variables are destroyed automatically, as soon as the script finishes.
Below is the syntax for creating and using Table variable.
You need to run the three statements together. If you try and run them separately you’ll see a message similar to the following: