SQL Server 2005 and Temporary Table Scope

When to use temporary table in SQL Server 2005

Most common scenario for using temporary tables is from within a stored procedure.

If there is logic inside a stored procedure which involves manipulation of data that cannot be done within a single query, then in such cases, the output of one query / intermediate results can be stored in a temporary table which then participates in further manipulation via joins etc to achieve the final result.

One common scenario in using temporary tables is to store the results of a SELECT INTO statement

The table variable is relatively new (introduced in SQL Server 2005 - as far as i can remember ) can be used instead of the temp table in most cases. Some differences between the two are discussed here

In a lot of cases, especially in OLTP applications, usage of temporary tables within your procedures means that you MAY possibly have business processing logic in your database and might be a consideration for you to re-look your design - especially in case of n tier systems having a separate business layer in their application.

In MS SQL Server 2005, what happens when a temp table is being accessed by different executions of the same SP?

In sql server if you create a local temp table it is with a single # sign sql server uses a few under-score and some ID in the back end. Say you create a Temp table with the name #Temp sql server in temp db Creates a Table with name #Temp_______10912098 , every Temp table created in separate connections will have their on ID in the end of the name.

These are all the Temp Tables Created in different Connections all has the name #Temp but are appended with some underscores and a unique id sql server uses to differentiate between them.

what will happen to my temp table in mssql if multiple users use my application?

Tables that start with the # are only active for the particular scope of the query, multiple users running the same query are in a different scope and thus could not affect each other. ##temp tables are global and other sessions can affect them.

Global temporary table scope behaves differently inside a stored procedure

Although I am not entirely sure why the code above works when outside the context of a stored procedure, I did determine that embedding all references to the global temporary table within the committed transaction allowed the stored procedure to work. So something like the following:

use db_name
go

create procedure dbo.proc_name
@sp_date date = NULL

as
if isnull(@sp_date,'') = ''
begin
  select @sp_date = getdate();
end

if object_id('tempdb..##global_tmp_tbl') is not null drop table ##global_tmp_tbl;

begin transaction

  set xact_abort on

  declare @query varchar(250), @exec_stmnt varchar(500);
  set @query = 'exec remote_db.dbo.remote_sp'  + ' ''''' + cast(@sp_date as varchar(10)) + ''''' ';
  set @query = '''' + @query + '''';

  set @exec_stmnt = 'select * into ##global_tmp_tbl from openquery(LS_RMT,' +  @query + ')';

  exec (@exec_stmnt);

if object_id('tempdb..#local_tmp_tbl') is not null drop table #local_tmp_tbl;
select * into #local_tmp_tbl from ##global_tmp_tbl;

commit transaction
go

SQL Insert Into Temp Table in both If and Else Blocks

The problem you’re having is not that you are populating the temp table, but that you’re trying to create the table. SQL parses your script and finds that you are attempting to create it in two different places, and so raises an error. It is not clever enough to realize that the “execution path” cannot possibly hit both of the create statemements. Using dynamic SQL will not work; I tried

DECLARE @Command  varchar(500)

DECLARE @Id int 
SET @Id = 2

IF OBJECT_ID('tempdb..#MyTestTable') IS NOT NULL DROP TABLE #MyTestTable 

IF (@Id = 2) BEGIN  
    SET @Command = 'SELECT ''ABC'' AS Letters INTO #MyTestTable'
END ELSE BEGIN 
    SET @Command = 'SELECT ''XYZ'' AS Letters INTO #MyTestTable'
END 

EXECUTE (@Command)

select * from #MyTestTable

but the temp table only lasts as long as the dynamic session. So, alas, it looks like you’ll have to first declare the table and then populate it. Awkward code to write and support, perhaps, but it will perform efficiently enough.

---

Related Topics