SQL Server - Need help in Temp table - Asked By Sumit Kumar on 29-Jan-10 06:21 AM

Hi all,
Could somebody explain me what is Temporary table in SQL Server 2000??? And what is the use of this table in real time application????

Thanks,
Sumit
mv ark replied to Sumit Kumar on 29-Jan-10 06:43 AM
Temporary tables have a limited lifetime. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE

Read more about Temporary tables here - http://msdn.microsoft.com/en-us/library/aa258255%28SQL.80%29.aspx

They can be used in stored procedures & sometimes improve performance

Kalit Sikka replied to Sumit Kumar on 29-Jan-10 06:54 AM

Introduction


Temporary tables are a useful tool in SQL Server provided to allow for short term use of data. There are two types of temporary table in SQL Server, local and global. 

Local temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. Global temporary tables are available to any connection once created, and are dropped when the last connection using it is closed.
Kalit Sikka replied to Sumit Kumar on 29-Jan-10 06:55 AM
http://www.sqlteam.com/article/temporary-tables
Jonathan VH replied to Sumit Kumar on 29-Jan-10 07:02 AM

Temporary tables are impermanent table objects created in the tempdb system database.  There are two types: local and global.  A local temporary tables, where its identifier starts with one #, is only visible within the connection session where it was created; it will be automatically dropped when the connection is closed.  A global temporary table, whose identifier starts with two ##, is visible across all connections and is dropped when all connections are closed (so when SQL Server is stopped).

Local temporary tables are useful as intermediate constructs in involved scripts or stored procedures (although table variables are another option), as they can increase performance by allowing control over how some operations are performed.  They can also be used to pass sets of data from a higher tier to the database tier (see my posts on this thread).

Global temporary tables are useful for holding state information and for passing sets of data from one application to another.