# [Temporary Table]
Ø The
name of this table starts with #.
Ø
Temp table can be accessed within the
declared stored procedure.
Ø The
scope of temp table does not exist beyond the stored procedure.
Ø Temporary
table will be dropped automatically on end of each session.
But it will always
be better to drop the table physically using the code
Ø Temp
tables can be created with the same name in multiple windows.
Ø Different
users can create diff temp tables with the same name.
Ø The
tables created will be having a unique id for each session.
Ø The
table name will be appended with the number allocated for that session.
Ø It
will always be better to create indexes on the temp tables and use that within
the stored procedures.
Ø Any
procedure with a temporary table cannot be pre-compiled
CREATE TABLE #Temp_TestTable
([TestTableID] [int] NOT NULL,
[FirstCol] [varchar](200) NULL,
[SecondCol] [int] NULL
)
GO |
DROP TABLE #Temp_TestTable
GO |
## [Global Temporary Table]
Ø The
name of this table will be started with ##.
Ø
Global temp table can be accessed across the sessions.
Ø
You can create the table in stored procedure and can
use this across multiple stored procedures.
Ø
Global temp tables are created with the same name
given by the user.
Ø
These are just like simple temporary tables but are
available to all sessions and will only be dropped automatically when last
session of database will be closed. If single session is active, global
temporary tables will remain available.
CREATE TABLE ##GTemp_TestTable
([TestTableID] [int] NOT NULL,
[FirstCol] [varchar](200) NULL,
[SecondCol] [int] NULL
)
GO |
DROP TABLE ##GTemp_TestTable
GO |