Changing Databases using ISQL/w

To change databases using Microsoft’s ISQL/w, you can select the database from the drop-down listbox at the top of the window. The next time you run a query, the program automatically changes your database context, then runs your query.

Changing Databases with DB-Library

If you are using DB-Library to write a program, you can use the dbuse() function (or, in Visual Basic, SQLUse()) to change database contexts:

r% = SQLUse(“pubs”)

Changing Databases with ODBC

If you are using ODBC, or something that ultimately makes ODBC function calls, like RDO or DAO, you cannot change your database context using a function call. You must use the USE command in a SQL query.

What Databases Are Available?

When the server is installed, several databases are automatically installed:

•  master

•  tempdb

•  model

•  msdb

The Master Database

The master database keeps track of all the information the server requires to run. It keeps track of user login information (in the syslogins table), another table, and even other databases (in sysdatabases), including their physical names, to which parts of the disk they are assigned, and various options the database has turned on. The master database is the default database context when a user logs in.

The master database is reserved for system data. Although you are permitted to add your own tables and other objects to the master database, such additions are rare.

tempdb

tempdb is the temporary database. Whenever you write a query that uses worktables (such as an ORDER BY), or you create a temporary table, that table goes in tempdb. This database is cleared out and rebuilt from scratch every time the server restarts.

---

Warning:  
If tempdb runs out of space, it can cause a lot of problems for all users. No one will be able to sort data with an ORDER BY; stored procedures that create temporary tables will not run.

---

model

The model database is used by the server as a database template when new databases are created. The model database contains a number of system tables that all databases inherit, such as sysobjects, which tracks all objects in a database. If you wanted to distribute a particular stored procedure or a particular table to every new database, you could make the change to model. Then, any time a new database was created, the objects you placed in model would be created in the new database.

Database options, such as select into/bulkcopy, are also inherited from model.

msdb

This database tracks backup and restore operations. Backup and restore operations are used to maintain back copies of the databases on a server. The database is used primarily by SQL-Executive, a program that Microsoft has written to assist administrators in managing SQL Server.

pubs

The pubs database is not automatically installed. A script (more on this in a moment) is copied to \mssql\install, which can later be run to install the pubs database. This database is the one we’ve been using to run all of the examples in this book. The data is intended for use as a learning tool.

User-Defined Databases

By far, the most interesting databases are the ones that you create and use. Any user databases that are on your system will show up in the listbox of databases in ISQL/w. You can also execute the sp_helpdb stored procedure to list available databases.

   sp_helpdb

 name       db_size     owner  dbid   created    status  
---------   --------    -----  ----   -------    --------------
bigpubs     60.00 MB sa    8   Dec 19 1996       trunc. log on chkpt.
iq_logs     15.00 MB sa    7   Dec 11 1996       no options set
iq_master   15.00 MB sa    6   Dec 11 1996       no options set
master      17.00 MB sa    1   Dec  4 1996       trunc. log on chkpt.
model        1.00 MB sa    3   Dec  4 1996       no options set
msdb         8.00 MB sa    4   Dec  4 1996       trunc. log on chkpt.
pubs         3.00 MB sa    5   Dec  4 1996       select into/bulkcopy
tempdb      22.00 MB sa    2   Jan 13 1997       select into/bulkcopy

If you want to retrieve only the names of databases, you can execute this query, which looks for names in the sysdatabases table. This table resides in the master database:

select name
from   master..sysdatabases

name
------------------------
bigpubs
iq_logs
iq_master
master
model
msdb
pubs
tempdb

This query references the sysdatabases table in the master database. The owner portion of the name is left out of the FROM clause, so the server looks for a sysdatabases table owned by me, then a sysdatabases table owned by dbo. Once the table is found, it retrieves the name from each row.

Batches

A batch is one or more SQL statements that are sent to the server to be compiled and executed.

In ISQL/w, a batch is executed when you hit Ctrl+E, or when you click on the green button to execute. If you include the word GO anywhere in the executed text, this indicates to the client program that it should send all the commands that came before GO, wait for a result, and then continue on with the commands after it.

Order of Execution

When the server receives a batch of commands, it puts the SQL code through a five-step process:

1.  Parse: Syntax—The commands are checked for syntactic validity. Do the commands make sense?

2.  Parse: Object references—If the syntax is okay, check for referenced objects. Do the referenced tables exist? Do you have permission to access them? Do the columns exist in those tables?

3.  Optimize—The server considers different plans for getting and processing the data. Are indexes available? Can they be used effectively, or would a table scan be faster? This is a very complex process, which is being constantly improved. The result is a query plan detailing how to access the data in the fastest way.

4.  Compile—The query plan is compiled.

5.  Execute—The compiled plan is executed at the server.