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 a specific database as 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 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.