Global Vs Local Temporary Table

 

Temporary table is a very important part for SQL developer. Here in this article we are focusing about the local and global temporary table.

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

The global temporary table is created by putting (##) prefix before the table name.

CREATE TABLE ##sample_Table

                          (emp_code  DECIMAL(10)   NOT NULL,

                           emp_name  VARCHAR(50) NOT NULL)

A local temporary table, like #California below, is visible only the local session and child session created by dynamic SQL (sp_executeSQL). It is deleted after the user disconnects.

The local temporary table is created by putting (#) prefix before the table name.

CREATE TABLE #sample_Table

                         (emp_code  DECIMAL(10)   NOT NULL,

                          emp_name  VARCHAR(50) NOT NULL)

 

If we use the block like BEGIN TRANSACTION and COMMIT TRANSACTION/ROLLBACK TRANSACTION the scope of the Local temporary table is within the transaction not out site of transaction.

Hope the article is quite informative and thanking you to give your valuable time.

Posted by: MR. JOYDEEP DAS