Temporary Tables

Temporary Tables

# tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When we close this session, the table will be automatically dropped. 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.



CREATE TABLE #TempTable (
Id int,
Desc char(30) )

select name
from tempdb..sysobjects
where name like '#TempTable%'

name
------------------------------------------------------------------------------------
#TempTable_________________________ . . .________00000000001D

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 primary keys, DEFAULTs . You can also add and drop columns from temporary tables.


ALTER TABLE #TempTable ADD CONSTRAINT [DF_Desc] DEFAULT ('Test') FOR [Desc]

ALTER TABLE #TempTable add PID int DEFAULT (10) not null

ALTER TABLE #TempTable ADD PRIMARY KEY CLUSTERED ([PID])
GO

CREATE nonclustered index Ix_desc on #TempTable([Desc])



SQL Server stores the object with a some type of unique number appended on the end of the name. It does all this for you automatically. You just have to refer to #TempTable in your code.

If two different users both create a #TempTable table each will have their own copy of it. The exact same code will run properly on both connections. Any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing. If stored procedure P1 creates a temporary table and calls stored procedure P2, then P2 will be able to use the temporary table that P1 created. It's generally considered good coding practice to explicitly drop every temporary table you create. If you are running scripts through SQL Server Management Studio or Query Analyzer the temporary tables are kept until you explicitly drop them or until you close the session.

Local temporary tables are visible only in the current session... ... Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE.

Scopes/Sessions are an interesting thing in sql server.
They follow the rule of inheritance, same as transactions:
A child has all parents' "objects" plus it's own.
So what this means is that you can access a #temp table created
in the parent process from the child of that parent,
but not vice versa.
Any dynamic SQL execution with either exec() or sp_executeSQL is ran in
a child session/scope of the current one.

To check this, pl run below set of queries.

Set1:

exec('select * into #TempTable2 from #TempTable; select * from #TempTable2')
select * from #TempTable2
drop table #TempTable2

Set2:

select * into #TempTable2 from #TempTable
exec('select * from #TempTable2')
select * from #TempTable2
drop table #TempTable2

You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

Global Temporary Tables

You can also create global temporary tables. These are named with two pound signs. For example, ##TempTable is a global temporary table. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.


Table variables are similar to temporary tables except with more flexibility and they always stay in memory.Table variables don't need to be dropped when you are done with them.