Create temporary tables per session in a DB other than tempdb

Question

Using SQL Server, Is there a way to create a temporary table per user session in a user database in the same exact way they are created in tempdb?

Using tempdb is fine in most cases, but some of the operations I've been seeing generate a lot of IO and in some cases end up saturating the templog file.

So here is a typical situation

-- create the table
create table #some_table(t int)

-- Do some work
declare @rownum int = 1

begin transaction
while 1=1
begin
    insert into #some_table values(@rownum)
    if @rownum > 5000000
        break

    set @rownum = @rownum + 1

end

Work continues on until @rownum reaches 5000000 and because its within a transaction, tempdb can't shrink. In the mean time, alerts start showing up and the LOGS disk fills up. Doing a SHRINKFILE is useless because the transaction is left open.

The work has to be done in tempdb because the process that created that temporary table could be run multiple times or there could be multiple processes that use the same table name.

-- show the contents of #some_table
set statistics io on

select * from #some_table

set statistics io off   


-- results
(5000001 row(s) affected)
Table '#some_table_________________________________________________________________________________________________________00000000001F'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So until I find a way to simulate that temporary table per session ability in tempdb, we have to continue tracking down offending queries and basically play air traffic control with tempdb.

Here is the situation I'm currently dealing with

tempdb's log is currently at 40GB+ and climbing fast.

Answer 1

There is no direct way to copy temp table behavior into non user databases. This starts with the easy collision preventions and continues to several advantages of temp tables (like reduced logging) that just can't be reproduced.

To create data sets in user databases that avoid collisions you have basically two options:

1. put all the data in one table and deal with the separation using some kind of session id in the table

The most obvious session id you could use for the single table approach is the @@SPID. But you could use other "random" values too. To achieve greater physical separation, you could partition the table on that column. The obvious disadvantage is that all sessions have to use the same table layout.

2. create a random table name and use dynamic sql from then on.

The random-table-name option is going to make your code very hard to read. But as SQL Server does not allow statements to access objects based on names stored in variables, dynamic SQL is your only option.

If you go this way I recommend to include the current session_id and request_start_time (from sys.dm_exec_requests) as well as a guid in the name. That way it is easy to write some kind of garbage collector that goes around and deletes tables that got left behind. Depending on your object life time expectations you might have to use a different time stamp like the connect_time in sys.dm_exec_connections but the idea is the same. Once the request with that start time is gone, the object cannot be of use anymore because the variable holding its name went out of scope too.