Local temp table and Global temp table

What is Local temp table and Global temp table

Temp tables are like a physical table but it’s always creating in tempdb database and its name start with hash (#), It’s automatically drop when the procedure is finished or connection close and also can drop explicitly using drop command. 

There are two varieties of temp tables: 1st Local & 2nd Global temp table.

Temp table provide fully DDL and DML operations like transections, explicitly and implicitly Clustered and non-clustered indexes, add & drop column, manipulate data (insert, update and delete) etc.   

We can’t access temp tables in side of functions but can use inside of procedure.  

Scope of Local Temp Table:

1. Its create with single hash (#)
2. Local temp tables are only accessible in same connection or session.
3. We can create 1 or more Local and global temp tables in same session and  Local temp table access in same session so table names are unique in same session but we can use same name in other session and sql server itself add random number with local temp table name so no need to manage unique name in globally on entire server

Like I have create same table in two Session

Session 1: CREATE TABLE #temp_1(ID BIGINT IDENTITY PRIMARY KEY,name varchar(100));

Session 2: CREATE TABLE #temp_1(ID BIGINT IDENTITY PRIMARY KEY,name varchar(100));

Their both tables created with #temp_1 name in both session but sql server add random number with local temp table name

Scope of Global Temp Table:

1. its name start with double hash (##)
2. Global temp tables can access in other all sessions/connections  
3. table will drops when the connection(where table created) is closed
4. its name must be unique like physical table bcz there random number not adding with this kinds of tables name, its globally access like :

CREATE TABLE ##temp(ID BIGINT IDENTITY PRIMARY KEY,name varchar(100));

• global temp table we can access in child procedure and process like

Exp.1

Session: 1

CREATE proc sp_t1
as
begin
        CREATE TABLE ##tt(ID BIGINT IDENTITY PRIMARY KEY,name varchar(100));
        insert into ##tt VALUES (‘MM Gehlot’);
        SELECT * FROM ##tt;
End

Session: 2

exec sp_t1;

Session: 3

SELECT * FROM ##tt;

It will be return 1 row because Session-2 and it’s have create ##tt table inside of procedure and still session is open but when session will close or explicitly drop then it will print error message like table not exists.

Exp.2

Session: 1

CREATE proc sp_t1
as
begin
        SELECT * FROM ##tt;
End
 
CREATE proc sp_t2
as
begin
        CREATE TABLE ##tt(ID BIGINT IDENTITY PRIMARY KEY,name varchar(100));
        insert into ##tt VALUES (‘MM Gehlot’);
exec sp_t1;
End

Session: 2

exec sp_t2;

return result set

Session: 3

SELECT * FROM ##tt;

Their ##tt table also accessible and return result set if session 2 is open and explicitly ##tt not drop.