Selecting a Database
Most objects in an instance of SQL Server are stored in databases. All references to database objects must be either explicitly or implicitly resolved to the specific database in which they reside.
Database References
Explicit database references occur when a Transact-SQL statement names the database that holds the data. The following example explicitly names the AdventureWorks2008R2 database:
SELECT BusinessEntityID, LoginID, NationalIDNumber
FROM AdventureWorks2008R2.HumanResources.Employee;
Implicit database references occur when a Transact-SQL statement does not specify the database, for example:
SELECT BusinessEntityID, LoginID, NationalIDNumber
FROM HumanResources.Employee;
To resolve implicit database references, SQL Server uses the concept of a current database. Every connection to an instance of SQL Server always has a database set as the current database. All object references that do not specify a database name are assumed to refer to the current database. For example, if a connection has AdventureWorks2008R2 set as its current database, any statement that refers to an object named Product is resolved to the Product table in AdventureWorks2008R2.
Default Database
Every SQL Server login has a default database. At the time the login is defined by a member of the sysadmin fixed server role, the default database for the login can be specified. If a default database is not specified, the master database becomes the default database for the login. The default database for a login can be changed later by using the sp_defaultdb stored procedure.
When you first connect to an instance of SQL Server, the default database for the login is typically made the current database. However, you can specify a specific database as the current database at connect time. This request overrides the default database designated for the login. You can specify a database on a connect request in the following ways:
In the sqlcmd utility, specify the database name by using the /d switch.
In ADO, specify the database name in the Initial Catalog property of an ADO connection object.
In the SQL Server Native Client OLE DB provider, specify the database name in the DBPROP_INIT_CATALOG property.
In the SQL Server Native Client ODBC driver, you can set a database name in an ODBC data source by using the Database box of the Microsoft SQL Server DSN Configuration Wizard or the DATABASE = parameter on a call to SQLConfigDataSource. You can also specify DATABASE = on a call to SQLDriverConnect or SQLBrowseConnect.
Selecting a Database
You can switch the current database setting while you are connected to an instance of SQL Server. This is referred to as using, or selecting, a database. You can switch the current database in the following ways:
Execute the Transact-SQL USE database_name statement, regardless of the database API that an application is using.
In SQL Server Management Studio, click New Query. From the query toolbar, select a database from the list of available databases.
In the SQL Server Native ClientODBC driver, call SQLSetConnectAttr to set the SQL_ATTR_CURRENT_CATALOG connection attribute.
Note
In many of the Transact-SQL reference examples in SQL Server Books Online, the first line of code in the example is USE AdventureWorks2008R2. This guarantees that the example is executed against the explicitly specified database instead of the current database context.