What is the difference between a Local and a Global temporary table?

Posted by Virendradugar on 9/9/2009 | Category: Sql Server Interview questions | Views: 10476

Post |

Interview FAQs |

Exclusive Questions |

Interview Home

Answer:

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

Global temporary tables (created with a double “##”) are visible to all sessions. You should always check for existence of the global temporary table before creating it… if it already exists, then you will get a duplicate object error.

Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it.

Asked In: Many Interviews | Alert Moderator

Bookmark It

< Previous : How to get @@ERROR and @@ROWCOUNT at the same time ...

Next > : What is Row_Number()?

Comments or Responses

Posted by: Pandians on: 9/10/2009

Hi Virendradugar

For your kind attention :

The definition is WRONG about Global Temporary Table.

1. Global temporary table(##) will be available thru all connections. ( Until the Source connection(Where the ##table created) is closed).

2. Once the source connection(Where the #table/##table created) is closed the Local temporary(#) table and Global tempory(##) table also will be destroyed.

3. But, the Local temporay table available only in source connection(Where the #table created), Not for other connections.

Your definition is :
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears . However, the table definition remains with the database for access when database is opened next time.

Pl verify the BOLDED statements in your definition and kindly correct it.

Cheers

More Interview Questions by Virendradugar

Latest Interview Questions