Connecting to and Disconnecting from an Instance

An application must connect to an instance of Microsoft® SQL Server™ before it can work with a SQL Server database. Connection occurs through a component such as shared memory or a network. An application can open multiple connections to an instance of SQL Server.

After a connection is made, the application can execute Transact-SQL statements through the connection. After an application completes all the work that must be done in an instance of SQL Server, the application disconnects. This frees all resources held by the connection in the server and terminates the network or shared-memory connection between the application and the instance.

In general, connections:

• Are associated with a database, which the application can change as needed. Object references that do not specify a database are assumed to be in the current database associated with the connection.
  
  
• Are associated with a specific login account. The login account is associated with user IDs in the SQL Server databases. A connection cannot perform an action in a database that is not permitted to the user ID associated with the connection's login account.
  
  
• Are the units of transaction control. If a connection is broken, all uncommitted modifications made by statements executed through the connection are rolled back without affecting uncommitted modifications made through other connections opened by the same application. Locks held by one connection opened by an application prevent the locked rows from being worked on by other connections opened by the same application.
  
  
• Have attributes, such as the transaction isolation level, which can be set by the application to specify connection-level behaviors.

Internet applications also connect to an instance of SQL Server, either through an ADO or OLE DB connection, or by specifying a SQL Server virtual root in a Uniform Resource Locator (URL). While the Internet application uses Transact-SQL statements or XPath queries that use XML documents instead of relational result sets, all of the characteristics listed above still apply to the connection.