Export (0) Print
Expand All
1 out of 6 rated this helpful - Rate this topic

Selecting a Database

Most objects in an instance of Microsoft SQL Server 2005 are stored in databases. All references to database objects must be either explicitly or implicitly resolved to the specific database in which they reside.

Explicit database references occur when a Transact-SQL statement names the database that holds the data. The following example explicitly names the AdventureWorks database:

SELECT EmployeeID, LoginID, ManagerID
FROM AdventureWorks.HumanResources.Employee

Implicit database references occur when a Transact-SQL statement does not specify the database, for example:

SELECT EmployeeID, LoginID, ManagerID
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 AdventureWorks set as its current database, any statement that refers to an object named Product is resolved to the Product table in AdventureWorks.

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 Native Client OLE DB provider, specify the database name in the DBPROP_INIT_CATALOG property.
  • In the SQL 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.

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 Native ClientODBC driver, call SQLSetConnectAttr to set the SQL_ATTR_CURRENT_CATALOG connection attribute.
    ms180770.note(en-US,SQL.90).gifNote:
    In many of the Transact-SQL reference examples in SQL Server Books Online, the first line of code in the example is USE AdventureWorks. This guarantees that the example is executed against the explicitly specified database instead of the current database context.

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.