What is Temporary Tables and Table Variables

Temporary Tables

Temporary tables are created in tempdb.  The name "temporary" is slightly misleading, for even though the tables are instantiated in tempdb, they are backed by physical disk and are even logged into the transaction log.  They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements.  If created inside a stored procedure they are destroyed upon completion of the stored procedure.  Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user.  Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another - they would remain autonomous transactions and the tables would remain autonomous objects.  You may notice that my sample temporary table name started with a "#" sign.  This is the identifier for SQL Server that it is dealing with a temporary table.

The syntax for creating a temporary table is identical to creating a physical table in Microsoft SQL Server with the exception of the aforementioned pound sign (#):

CREATE TABLE dbo.#Company    (    Eid int NOT NULL,    Ename varchar(10),    Elname varchar(20),    age int,    JDate datetime    )

Temporary tables act like physical tables in many ways.  You can create indexes and statistics on temporary tables.  You can also apply Data Definition Language (DDL) statements against temporary tables to add constraints, defaults, and referential integrity such as primary and foreign keys.  You can also add and drop columns from temporary tables.  For example, if I wanted to add a default value to the DateEntered column and create a primary key using the Car_id field I would use the following syntax:

ALTER TABLE dbo.#Company    ADD        CONSTRAINT [DF_JDate] DEFAULT (GETDATE()) FOR [JDate],        PRIMARY KEY CLUSTERED            (                [Eid]            )  ON [PRIMARY] GO