|
|
| Re: what are Global Temporary Tables [message #166980 is a reply to message #166797] |
Mon, 10 April 2006 12:17  |
amankhan
Messages: 69
Registered: December 2005
Location: Texas
|
Member |
|
|
Hi Sonali,
You can use Globle temp tables throw multi transaction
blocks. but temp table cant . they will be distroyed.!
Refer to the following link for detail information:-->
http://lists.isb.sdnpk.org/pipermail/comp-list/2003-January/001741.html
Extra information:
====================
===================================
Temporary Tables
You can also create temporary tables. Temporary tables are similar to permanent tables, except temporary tables are stored in 'tempdb' and are deleted automatically when no longer in use.
The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances of Microsoft® SQL Server™ 2000. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server.
For example, if you create a table named employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If you create a local temporary table named #employees, you are the only person who can work with the table, and it is deleted when you disconnect. If you create a global temporary table named ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it when both of you disconnect.
=================================
Sometimes you'll need to use temporary tables to accomplish a particular
process. There are two types of temporary tables: local and global. The
differences between the two tables are scope and session dependency.
Local temporary tables are denoted by a single pound sign, #, and the
global
temporary tables are denoted by double pound sign, ##.
The local temporary table is only visible for the current session and is
available throughout the session's activity, unless specifically dropped
within the session.
The global temporary table is available to all sessions on the server
instance's sessions and is available for the duration of the session. A
global temporary table can be created by any session that has the
permissions to CREATE TABLE.
It should be noted that you can't create a foreign key constraint on
either type of temporary table. You will receive the message below when
attempting to do so:
"Skipping FOREIGN KEY constraint '<foreign key constraint name>'
definition for temporary table."
Also, if you prefix either type of temporary table with a database or
owner name, SQL Server will create the temporary tables in the tempdb
database with the dbo as owner. In other words, SQL Server will disregard
the
prefixed database and owner name. If you attempt to create or access
either type of temporary table, you will receive the following message:
"Database name '<database name>' ignored, referencing object in tempdb."
The following sample script demonstrates how to create both local and
global temporary tables. In addition, there are examples of attempting to
create a non-dbo owned temporary table and trying to access a non-dbo
owned temporary table.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
