Presents your 
SQL SERVER E-NEWSLETTER for January 9, 2003

<------------------------------------------->

CREATE LOCAL AND GLOBAL TEMPORARY TABLES

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:

-- CREATE LOCAL TEMP TABLE
CREATE TABLE #TEMP1_LOCAL
(PKEY1 INT IDENTITY NOT NULL
     CONSTRAINT PK_TEMP1_LOCAL PRIMARY KEY,
COL1 INT NULL)
-- CREATE GLOBAL TEMP TABLE
CREATE TABLE ##TEMP1_GLOBAL
(PKEY1 INT IDENTITY NOT NULL
     CONSTRAINT PK_TEMP1_GLOBAL PRIMARY KEY,
COL1 INT NULL)
-- CREATE SQL USER IN NORTHWIND DATABASE
EXEC sp_addlogin temp_user, 'password', NORTHWIND
EXEC NORTHWIND.DBO.sp_adduser temp_user
EXEC NORTHWIND.DBO.sp_addrolemember db_owner, temp_user
-- CREATE LOCAL TEMP TABLE USING CREATED SQQL USER
CREATE TABLE NORTHWIND.temp_user.#TEMP2_LOCAL
(PKEY1 INT IDENTITY NOT NULL
     CONSTRAINT PK_TEMP2_LOCAL PRIMARY KEY,
COL1 INT NULL)
-- CREATE GLOBAL TEMP TABLE USING CREATED SSQL USER
CREATE TABLE NORTHWIND.temp_user.##TEMP2_GLOBAL
(PKEY1 INT IDENTITY NOT NULL
     CONSTRAINT PK_TEMP2_GLOBAL PRIMARY KEY,
COL1 INT NULL)
-- FINDING TEMP TABLES IN EITHER TEMPDB OR  NORTHWIND DATABASE
SELECT type, name FROM TEMPDB.DBO.sysobjects WHERE type = 'U'
SELECT type, name FROM NORTHWIND.DBO.sysobjects WHERE type = 'U' AND
crdate > DATEADD(mi, -1, CURRENT_TIMESTAMP)
-- FINDING SQL USERS IN TEMPDB DATABASE THAAT COULD CREATE TABLES
SELECT name, * FROM TEMPDB.DBO.sysusers WHERE issqlrole = 0
-- ATTEMPTING TO SELECT LOCAL TEMP TABLE USSING CREATED SQL USER IN
NORTHWIND
SELECT 'temp_user table' temp_user_table, * FROM
NORTHWIND.temp_user.#TEMP2_LOCAL
-- CLEANUP - DROPPING TABLES
DROP TABLE #TEMP1_LOCAL, ##TEMP1_GLOBAL, #TEMP2_LOCAL, ##TEMP2_GLOBAL
-- CLEANUP - DROPPING SQL USERS
EXEC NORTHWIND.DBO.sp_droprolemember db_owner, temp_user
EXEC NORTHWIND.DBO.sp_dropuser temp_user
EXEC sp_droplogin temp_user
GO

----------------------------------------