Difference between Session and Connection in SQL S

Question

In case of Temporary Tables,we see that they are connection dependent,I mean Tables created in one connection is only available to that connection and automatically dropped when the connection is lost or destroyed.

What are the difference between connection and session in SQL Server?

Answer 1

Literally : Connection is Physical Communication Channel and Session is a state of information exchange. A Connection may have multiple sessions.

The connection is the physical communication channel between SQL Server and the application: the TCP socket, the named pipe, the shared memory region. The session in SQL Server corresponds to the Wikipedia definition of a session: a semi-permanent container of state for an information exchange. In other words the sessions stores settings like cache of your login information, current transaction isolation level, session level SET values etc etc.

Normally there is one session on each connection, but there could be multiple session on a single connection (Multiple Active Result Sets, MARS) and there are sessions that have no connection (SSB activated procedures, system sessions). There are also connections w/o sessions, namely connections used for non-TDS purposes, like database mirroring sys.dm_db_mirroring_connections or Service Broker connections sys.dm_broker_connections.

I got the reference from here

Answer 2

common concepts that get used with SQL Server thread management and scheduling :

Sessions – when the client application connects to SQL Server the two sides establish a “session” on which to exchange information. Strictly speaking a session is not the same as the underlying physical connection, it is a SQL Server logical representation of a connection. But for practical purposes, you can think of this as being a connection (session =~ connection). See sys.dm_exec_sessions. This is the old SPID that existed in SQL Server 2000 and earlier. You may sometimes notice a single session repeating multiple times in a DMV output. This happens because of parallel queries. A parallel query uses the same session to communicate with the client, but on the SQL Server side multiple worker (threads) are assigned to service this request. So if you see multiple rows with the same session ID, know that the query request is being serviced by multiple threads.

-

Connections – this is the actual physical connection established at the lower protocol level with all of its characteristics sys.dm_exec_connections . There is a 1:1 mapping between a Session and a Connection.