Global Temporary Table

Hi Shubham,

Global temporary table:- Global temporary table is created in the tempdb and it is visible to all other sessions as well. Global temporary tables are only destroyed when the current user disconnected and all the sessions referring to it closed. It means no user sessions refers it. Since the global temporary table is created in the tempdb, whenever we use temporary tables there is a interaction between the two database (tempdb and the database in which block of code is written) which may slow down the performance. Global temporary table can be used in Joins. Global table is created with the help of the ##.

The syntax for creating the global temporary table is given below:

create table ##[table name] (col1 datatype, col2 datatype, ......coln datatype)

For example, the syntax to create the global temporary table ##employeedetails is given below:

CREATE table ##employeedetails (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)

We can also create indexes on the Global temporary tables. For example,

Create index indx_GTT on ##employeedetails (empFname)

We can also define constraints on the Global temporary tables. For Example

Alter table ##employeedetails add constraint pk_GTT primary key(id)

Similar to physical table and local temporary table, the values in the global temporary table can also be inserted with the help of the Insert command. For example, to insert the value in the global temporary table ## employeedetails , we can use the following SQL Statement:

Insert into ## employeedetails ( empFname , empEname , empdate )
Values ( 'Vivek', ' Johari', getdate())

We can access the global table within the stored procedure in which it is defined and also in the stored procedure which is called inside this stored procedure.

Create procedure test_GTT
as
begin
CREATE table ##employeedetails (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)
exec insert_GTT

select * from ##employeedetails
drop table ##employeedetails
end

Create procedure insert_GTT
as
begin
Insert into ##employeedetails ( empFname , empEname , empdate )
Values ( 'Vivek', ' Johari', getdate())
end

In the above example, we create a global temporary table ##employeedetails
in the stored procedure test_GTT. This procedure call another procedure insert_GTT. The procedure contains the Insert command for the table ##employeedetails. When we execute the procedure test_GTT, it will give the following result.

Exec test_GTT

Global temporary tables are itself dropped when the current user session is closed and no other user is referring to it, but it is better to drop it manually at the end of the block of the code in which it is defined or at the end of the stored procedure in which it is defined.

The global temporary tables can viewed with the help of the system view 'sys.objects'.

SELECT * FROM sys.objects where type='U'

Transactions are also applicable on the Global temporary tables.

Alter procedure test_GTT
as
begin
begin transaction

CREATE table ##employeedetails (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)

exec insert_GTT

rollback

select * from ##employeedetails

drop table ##employeedetails

end
If we execute the above procedure the it will give the following output.

Limitations:-

We can't use global temporary table inside a SQL Function.
We can't create triggers on the temporary tables.
We can create View on the temporary tables.

Please refer the below link

http://www.sqlservercentral.com/blogs/vivekssqlnotes/2012/01/07/sql-server-global-temporary-tables/

Thanks

Hi Shubham,

A global temporary table is visible to any session (connection on server) while in existence. It lasts until all users that are referencing the table disconnect.

A local temporary table, like #California below, is visible only the local session (connection) and child session created by dynamic SQL (sp_executeSQL). The temporary table is localized by an auto-generated suffix which is added to the table name. It is deleted after the user disconnects.

In some applications global temporary tables offer better performance than local or table variables.

A global temporary table is created using CREATE TABLE statement with the table name prefixed with a double number sign (##table_name).

In SQL Server, global temporary tables are visible to all sessions (connections). So if you create a global temporary table in one session, you can start using it in other sessions.

Session 1

CREATE TABLE ##temp1

(c1 INT);

INSERT INTO ##temp1

VALUES (1);

Session 2

SELECT * FROM ##temp1

For example,

SELECT * INTO ##tmpemp FROM employees

Now the all sql server connection sessions can use this ##tmpemp table.

For more info:
http://www.sqlines.com/articles/sql-server/local_and_global_temporary_tables

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak14y ago
Hi Shubham,

Global temporary table:- Global temporary table is created in the tempdb and it is visible to all other sessions as well. Global temporary tables are only destroyed when the current user disconnected and all the sessions referring to it closed. It means no user sessions refers it. Since the global temporary table is created in the tempdb, whenever we use temporary tables there is a interaction between the two database (tempdb and the database in which block of code is written) which may slow down the performance. Global temporary table can be used in Joins. Global table is created with the help of the ##.

The syntax for creating the global temporary table is given below:

create table ##[table name] (col1 datatype, col2 datatype, ......coln datatype)

For example, the syntax to create the global temporary table ##employeedetails is given below:

CREATE table ##employeedetails (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)

We can also create indexes on the Global temporary tables. For example,

Create index indx_GTT on ##employeedetails (empFname)

We can also define constraints on the Global temporary tables. For Example

Alter table ##employeedetails add constraint pk_GTT primary key(id)

Similar to physical table and local temporary table, the values in the global temporary table can also be inserted with the help of the Insert command. For example, to insert the value in the global temporary table ## employeedetails , we can use the following SQL Statement:

Insert into ## employeedetails ( empFname , empEname , empdate )
Values ( 'Vivek', ' Johari', getdate())

We can access the global table within the stored procedure in which it is defined and also in the stored procedure which is called inside this stored procedure.

Create procedure test_GTT
as
begin
CREATE table ##employeedetails (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)
exec insert_GTT

select * from ##employeedetails
drop table ##employeedetails
end

Create procedure insert_GTT
as
begin
Insert into ##employeedetails ( empFname , empEname , empdate )
Values ( 'Vivek', ' Johari', getdate())
end

In the above example, we create a global temporary table ##employeedetails
in the stored procedure test_GTT. This procedure call another procedure insert_GTT. The procedure contains the Insert command for the table ##employeedetails. When we execute the procedure test_GTT, it will give the following result.

Exec test_GTT

Global temporary tables are itself dropped when the current user session is closed and no other user is referring to it, but it is better to drop it manually at the end of the block of the code in which it is defined or at the end of the stored procedure in which it is defined.

The global temporary tables can viewed with the help of the system view 'sys.objects'.

SELECT * FROM sys.objects where type='U'

Transactions are also applicable on the Global temporary tables.

Alter procedure test_GTT
as
begin
begin transaction

CREATE table ##employeedetails (id int identity(1,1), empFname nvarchar(100), empEname nvarchar(100), empdate datetime)

exec insert_GTT

rollback

select * from ##employeedetails

drop table ##employeedetails

end
If we execute the above procedure the it will give the following output.

Limitations:-
1. We can't use global temporary table inside a SQL Function.
2. We can't create triggers on the temporary tables.
3. We can create View on the temporary tables.
Please refer the below link

http://www.sqlservercentral.com/blogs/vivekssqlnotes/2012/01/07/sql-server-global-temporary-tables/

Thanks
Senthilkumar14y ago
Hi Shubham,

A global temporary table is visible to any session (connection on server) while in existence. It lasts until all users that are referencing the table disconnect.

A local temporary table, like #California below, is visible only the local session (connection) and child session created by dynamic SQL (sp_executeSQL). The temporary table is localized by an auto-generated suffix which is added to the table name. It is deleted after the user disconnects.

In some applications global temporary tables offer better performance than local or table variables.

A global temporary table is created using CREATE TABLE statement with the table name prefixed with a double number sign (##table_name).

In SQL Server, global temporary tables are visible to all sessions (connections). So if you create a global temporary table in one session, you can start using it in other sessions.

Session 1

CREATE TABLE ##temp1
(c1 INT);
INSERT INTO ##temp1
VALUES (1);
Session 2

SELECT * FROM ##temp1

For example,

SELECT * INTO ##tmpemp FROM employees

Now the all sql server connection sessions can use this ##tmpemp table.

For more info:
http://www.sqlines.com/articles/sql-server/local_and_global_temporary_tables