Temp Tables in MSSQL Database

Different types of temp Tables in Exists in MSSQL Database:

1) # TABLE (Local Temporary Tables)

This types of temp table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name.

CREATE TABLE #TABLE
(
rowid INT,
fname VARCHAR(200)
)

2) ##TABLES  (Global Temporary Tables)

This types of Global temporary tables operate much like local temporary tables; they are created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all sessions, until the creating session goes out of scope (and the global ##temp table is no longer being referenced by other sessions)

If the session is running and other session try to create the Global Temp Table than second session will get a error.
“There is already an object named ##TABLE in database. ”

CREATE TABLE ##TABLE
(
rowid INT,
fname VARCHAR(200)
)

3) @TABLES (Table Variables )

This types of table variable is created in memory, and so performs slightly better than #temp tables ( less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

DECLARE @table TABLE
(
rowid INT,
fname VARCHAR(200)
)