How It Works: Temporary Objects – User Transaction Scope

Temporary tables (create table #tmp, select into #tmp, declare @t table, internal worktables from sorts, internal spills from hashes, table valued parameters, cursors, etc.) are commonplace for SQL Server applications. There are many discussions pertaining to temporary table caching, recompile and such aspects. This post discusses physical metadata storage as it relates to performance.


There are many ways to create and use temporary tables, here is one example:


declare @t table (id int NOT NULL PRIMARY KEY CLUSTERED, name sysname)

insert into @t select object_id, name from sys.objects
select * from @t


The example uses a temporary table