Local And Global Table in SQL

ADVERTISEMENT

---

Difference between local temporary table and global temporary table are :

#TABLE(Local Temp table)

1. Temp tables are available ONLY to the one execution  that created it and are dropped when the process end.
2. Local temporary tables are visible only in the current session.
3. It accepts the single hash value "#" as the prefix when created. Syntax: (CREATE TABLE #t). They are visible only to the connection that creates it, and are deleted when the connection is closed.
4. It starts with single hash value "#" as the prefix of the table name.
5. A Local Temporary Table is only for the connection in which it was created.
6. You can create a Local Temporary Table with the same name but in a different connection, and it is stored with the same name along with various random values.

##TABLE(Global Temp table)

1. Temp tables (global) are available to ALL sessions, but are still dropped when the session that created it is closed and all other references to them are closed.
2. Global temporary tables are visible to all sessions. Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them.
3. Except two "##" values are used as the prefix at the time of their creation. Syntax: (CREATE TABLE ##table name). They are visible to all connections of SQL Server, and only destroyed when the last connection referencing the table is closed (in which we have created the Global Temporary Table).
4. It starts with the single hash value "##" as the prefix of the table name and its name is always unique. There is no random number appended to the name.
5. You can access the Global Temporary Tables from all connections of SQL Server until the referencing connection is open.