One of the things that I’ve often seen people do is build temp tables to hold data for each connection in a unique way. Back in older versions of SQL Server this was bad since large temp tables could lock sysobjects in tempdb and cause contention. That’s been fixed, but I still tend to avoid temp tables unless it’s truly something that is session dependent.

I’ve seen some loading routines that needed temporary or staging data. We used to have some of this in a financial services firm where we had dedicated machines that would pick up files and then load them. However since we weren’t sure which machine would load what data, we wanted a shared data source. The developers didn’t know what to do, or how to share a staging table, so they used temporary tables. This gave us an all-or-nothing approach since the temporary tables disappeared if the connection was lost.

We also needed a similar shared temporary storage at SQLServerCentral for mail processing. We wanted to pull out rows from a table for a particular sending process, and mark them as used by that process only. Since we had multiple machines that would act as senders, each of them generic, again, we wanted to share some type of “workspace” on SQL Server, but have it unique to each connection.

The way that I’ve handled both of these in the past is to create a real table containing the structure I need and then add a column to it that identifies the session. If there’s an issue, you can see what rows were in process by a session and either assign them elsewhere, or return them to the pool.

As an example, let’s say you need to send lots of emails, but you want to store some of this data in SQL Server as it’s processed. I could create a table like this:

CREATE TABLE EmailSends
( EmailAddress varchar(20)
, SUBJECT varchar(200)
, Msg varchar(2000)
, SPID int
)

I have the basic data here to send an email. I can load this from other tables, and then add in the SPID. This column allows me to uniquely identify a session, and the process working with this table can determine which rows it should process. I could load this table like this:

INSERT EmailSends
SELECT 
EmailAddress, '', '', @@SPID
FROM EmailList

This inserts all the emails, and marks these rows as belonging to my SPID. If another connection ran this same query, they’d have rows marked with a different SPID. I could now do some processing on “my” rows like this:

UPDATE dbo.EmailSends
SET Subject = a.SUBJECT
 FROM EmailSubjects a
WHERE a.EmailID = 1
AND EmailSends.SPID = @@SPID

What happens if there’s data in this table with my SPID that isn’t mine? That can’t happen if you do this both at the beginning and end of your connection:

DELETE dbo.EmailSends
WHERE SPID = @@SPID

It’s a crude technique, and doesn’t necessarily fit a lot of situations, but if you want to use this to manage workloads, maybe redistribute things to different processes, this can help you.