In SQL Server, temporary tables are created using the same CREATE TABLE syntax as regular tables. The difference is that temporary tables’ names are prefixed with either one or two number signs (#), depending on whether it’s a local temporary table or global temporary table:
- Local temporary tables are prefixed with a single number sign (
#) - Global temporary tables are prefixed with a double number sign (
##)
Example 1 – Create a Local Temporary Table
Here’s an example of creating a local temporary table:
CREATE TABLE #Cats (
CatId int PRIMARY KEY,
CatName varchar(70)
);
The name you specify for a local temporary table can be a maximum of 116 characters. Other table types can be 128 characters, but local temporary tables are automatically appended with a system-generated numeric suffix.
Example 2 – Create a Global Temporary Table
Here’s an example of creating a global temporary table:
CREATE TABLE ##Cats (
CatId int PRIMARY KEY,
CatName varchar(70)
);
Global temporary table names can be a maximum of 128 characters. SQL Server doesn’t append a system-generated numeric suffix to global temporary tables like it does for local temporary tables, so you can use up the full space.
Example 3 – View the Temporary Tables
I can run the following query to see my newly created temporary tables.
SELECT name FROM tempdb.sys.tables WHERE name LIKE '#Cats%' OR name = '##Cats';
Result:
+----------------------------------------------------------------------------------------------------------------------------------+ | name | |----------------------------------------------------------------------------------------------------------------------------------| | ##Cats | | #Cats_______________________________________________________________________________________________________________000000000018 | +----------------------------------------------------------------------------------------------------------------------------------+
We can see that the local temporary table has had the system-generated numeric suffix appended, and this is why my query uses the LIKE operator for that table.
Example 4 – New Session
If I open a new session, create a local temporary table with the same name (Cats), then run this query again, I get this:
+----------------------------------------------------------------------------------------------------------------------------------+ | name | |----------------------------------------------------------------------------------------------------------------------------------| | ##Cats | | #Cats_______________________________________________________________________________________________________________000000000018 | | #Cats_______________________________________________________________________________________________________________000000000019 | +----------------------------------------------------------------------------------------------------------------------------------+
So multiple sessions can create a local table of the same name because SQL Server modifies that name by incrementing the suffix.
Example 5 – Same Session
However, if I try to create the local temporary table again in the same session, I get this:
Msg 2714, Level 16, State 6, Line 1 There is already an object named '#Cats' in the database.
And if I open yet another session, I’m successful, and when I query tempdb.sys.tables I get this:
+----------------------------------------------------------------------------------------------------------------------------------+ | name | |----------------------------------------------------------------------------------------------------------------------------------| | ##Cats | | #Cats_______________________________________________________________________________________________________________000000000018 | | #Cats_______________________________________________________________________________________________________________000000000019 | | #Cats_______________________________________________________________________________________________________________00000000001B | +----------------------------------------------------------------------------------------------------------------------------------+