Temporary tables in ms SQL Server

For complex queries, we can use nested and complex SQL statements, but sometimes it is more convenient and efficient to use temporary tables.

SQL Server supports temporary tables. A temporary table is a table whose names start with a pound sign. If the temporary table is not removed when the user disconnects, SQL Server automatically removes the temporary table. Temporary tables are not stored in the current database, but stored in the system database tempdb.

There are two types of temporary tables:
1) local temporary table: The name of the local temporary table starts with a single digit (#). They are only visible to the current user connection and are deleted when the user is disconnected from the Microsoft SQL Server 2000 instance.
2) Global temporary table: The name of the global temporary table starts with a mathematical sign (#). After being created, it is visible to all users. If these tables are not explicitly removed before the connection to create a global temporary table is disconnected, the tables are removed as long as all other tasks stop referencing them. When the connection to the global temporary table is disconnected, the new tasks cannot reference them any more. After the current statement is executed, the association between the task and the table is removed. Therefore, the global temporary table is removed as long as the connection to the global temporary table is disconnected.
For example, if you create a table named employees, anyone who has the security permission to use the table in the database can use the table unless it has been deleted. If you create a local temporary table named # employees, you can only perform operations on the table and delete the table when the connection is disconnected. If you create a global temporary table named # employees, any user in the table can perform operations on the table. If the table is not used by other users after you create it, delete it when you disconnect it. If the table is used by other users after creation, SQL Server deletes the table after all users are disconnected.

E.g.

Create Table # temptablename
Or
Select [Field 1, Field 2,...,] into # temptablename from table
Delete table:
Drop table # temptablename