In MSSQL (TSQL), Can I specify a contextual variable that lives in a connection string, but does not affect pooling?

To explain the need further, consider this scenario:

In a legacy system that relies heavily on TRIGGERS, we need to provide a token of some sort (let's call it "SessionID") to be inserted into some security log tables. This token is created in C开发者_运维问答# in the application server, and will be passed into all SQL commands.

SADLY, THIS REQUIREMENT ON TRIGGERS IS UNCHANGEABLE

So, because I have access to modify the connection string, I can (and have successfully proven that I can) use the "Application Name" token to provide this piece of information.

string connectionString = string.Format("SERVER=sql.example.com; "
    + "DATABASE=someDB; User ID=xyz; Password=123; Application Name={0}", sID);

Since this works, what's the problem?

The problem is very simple... the above works, but since we have thousands of users... our connection-pool is destroyed (since connection pools are made based on the connection string... basically, I need the connection pool to be based on everything except the Application Name property).

So, do you know of how I can either:

1. Set a property in the connection string that will NOT be included in pooling.
2. Set a contextual property for this connection some other way that is not incredibly heavy on performance.

As a side-note... I could open the connection right away and create a temp table with a single value:

SELECT 12345 AS SessionID INTO #context

But that seems incredibly overkill!

---

You would need to execute this rather than rely on the connection string, but rather than a temp table SET CONTEXT_INFO / CONTEXT_INFO()can be used to associate some arbitrary data against the current session/connection (and is available in triggers).

If its acceptable for the server to create the variable from some combination of user/time etc you could assign CONTEXT_INFO automatically from within a logon trigger, in which case you would not need to change your connection mechanic at all.

---

My understanding is that separate connections pools are created using the connection string as a key, so any contextual modification to the connection string will inevitably affect pooling

I have noticed the WorkstationId property on the SqlConnection class before - that might be a technique to introduce some contextualization?

            SqlConnection cn = new SqlConnection("CONNECTION_STRING");
            string identifier = cn.WorkstationId;

---

OK, so after doing much testing, I've come up with a solution.

1. Turn off connection pooling.
2. Use the "Workstation ID" property.

After doing some benchmarking, I was able to see that connection pooling is 50 times faster than not using pooling... but... not using pooling still only took 0.005 seconds per connection.

I'm willing to live with the nano-second performance loss to get the security logging that we need.

Oh, as a side point - connection pooling has MAJOR performance limitations if all of the connections are being used at the moment, since the 101th person in line will have to wait... so, in SOME instances... connection pooling is 1,000 times slower than not using pooling!

---

Unfortunately everything in the connection string will be taken for the pooling (i think they will use GetHashCode() to check if the string is already in the pool).