Choosing a Database

All objects in Microsoft® SQL Server™ are stored in databases. All references to SQL Server objects have to be resolved to the specific database in which they reside.

• Explicit database references occur when a Transact-SQL statement names the database holding the data. This example explicitly names the Northwind database:

  SELECT *

  FROM Northwind..Employees

    

• Implicit database references occur when a Transact-SQL statement does not specify the database:

  SELECT *

  FROM Employees

    

To resolve implicit database references, SQL Server uses the concept of a current database. Every connection to 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 Northwind set as its current database, any statement referring to an object named Products is resolved to the Products table in Northwind.

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, master becomes the default database for the login. The default database for a login can be changed later using the sp_defaultdb stored procedure.

When you first connect to SQL Server, the default database for the login is usually made the current database. You can, however, specify that a specific database be made the current database at connect time. This request overrides the default database designated for the login. Here are the ways you can specify a database on a connect request:

• In the osql and isql utilities, specify the database name using the /d switch.
• In ADO, specify the database name in the Initial Catalog property of an ADO connection object.
• In OLE DB, specify the database name in the DBPROP_INIT_CATALOG property.
• In ODBC, you can set a database name in an ODBC data source 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 at any time while you are connected to SQL Server. This is called using, or choosing, a database. Here are ways you can switch the current database:

• You can execute the Transact-SQL USE database_name statement, regardless of the database API an application is using.
• In SQL Server Query Analyzer, you can select a database in the Database list box at the top of the Query Window.
• In ODBC, you can call SQLSetConnectAttr to set the SQL_ATTR_CURRENT_CATALOG connection attribute.
• In DB-Library, you can call the dbuse function.

Note In many Transact-SQL reference examples, “USE pubs” or “USE Northwind” is the first line in the example. This ensures the examples are executed against the explicitly specified database instead of another database.

See Also