1

I have the following logic in one of my SPs:

if object_id('tempdb..##val','u') is null begin
    create table ##val (
                name varchar(512)
                ,val nvarchar(max)
    )
end

    insert ##val (name,val)values('some unique name','abcdef')

This question has answer that indicates checking if object_id(, but I have these concerns:

1) is this check thread-safe from race condition point of view ? Specifically, do I need to use sp_GetAppLock/sp_ReleaseAppLock ?

2) Does if condition reserve reference on table ##val ? Specifically, is it possible that all other sessions release reference on ##val right after negative result of if condition making insert statement fail due to not existing ##val table ? And if yes how to reserve and keep reference on ##val from current session without locking rows in that table.

Community
  • 1
  • 1
alpav
  • 2,972
  • 3
  • 37
  • 47
  • 2
    Do you want multiple sessions to use the same copy of the ##global temp table? Since this effectively reduces concurrency to one (unless you want them to share data), this is why you rarely see global temp tables as a permanent solution. Are you sure you mean to use a global temp table? Why not just have a permanent table that always exists, then you don't have to check for existence etc.? – Aaron Bertrand Feb 18 '14 at 19:43
  • Yes, I want multiple sessions use the same copy of ##global temp table. I agree than permanent table is better solution, but I may be disallowed to create tables in specific database on the fly and also process of creating and modifying perm tables is quite complicated at my work place. Also sometimes I want to have global database independent meta-data to deal with multiple databases for migration/renaming scripts. – alpav Feb 18 '14 at 20:39

0 Answers0