Temporary tables in the SQL server

On the SQL server. Some people usually use temporary tables. Do you know how many types of temp tables there are on the SQL server?

There are 2 types of temp tables: Local & Global

1/ What is the difference between Local & Global temp tables?

Local temporary tables:

• Prefix local temporary table names with a single number sign (#table_name) They are visible in the current session.
• The table_name specified for a local temporary name can't exceed 116 characters
• All other local temporary tables are dropped automatically at the end of the current session.

Global temp tables:

• Prefix global temporary table names with a double number sign (##table_name).
• They are visible in all sessions.
• The table name can create more than 116 characters.
• The global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

2/ How to create the temporary tables.

• Local

?CREATE TABLE #MyTempTable (ID INT PRIMARY KEY);        

• Global

CREATE TABLE ##Glb_Temp_Table (ID INT PRIMARY KEY);        

3/ Where are the temp tables stored?

• The temp tables will be stored in the tempdb.
• The full name of the temporary tables will saved in the object sys.sysobjects

4/ Some rules for temporary tables.

• If more than one temporary table is created inside a single stored procedure or batch, they must have different names.
• If you create a table including the schema_name. It‘s ignored and all the temp tables will be created in the DBO schema.
• Temporary tables are automatically dropped when they go out of scope unless explicitly dropped by using DROP TABLE.