We use SQL authentication from a .Net 3.5 WinForms application to connect to SQL 2008 database. I assume ADO.Net uses connection pooling by default. We are using standard ADO.Net to connect to the database.
Lets assume the pool size is 100 (the default). In a hypothetical scenario, 100 users connect to the database using the same application. Then the 100th user disconnects thereby releasing the connection to the pool. Now another user connects to the database. Will these two users share SPID?
I tried this on my machine and using a database locally. Apparently two instances of the same application never share SPID. They did not appear to reuse even the connection pool.
I also read in another article that connection pools are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity. Each user will have their own process. So each user will use their own connection pool. So doesnt that defeat the purpose of connection pooling? Am I missing something?
We are using local temporary tables in sql server code and so I am a bit concerned two connections will try to use the same temporary table and cause inconsistent results and serious issues.