Day 11
Databases, Object Naming, and Batches

The queries so far have all been in the pubs database. In this chapter, you learn about other databases, including

•  What “database” means in SQL Server World

•  What databases are available on all MS-SQL systems

•  What databases are on my system

•  How and why to use different databases

In the second half of the chapter, I discuss batches. I’ve used multiple batches a little in the previous chapters—creating tables on Day 9, for example—but today I’ll show you everything about batches:

•  Including multiple statements in the same batch

•  Executing multiple batches

•  How to deal with multiple result sets

•  Order of execution at the server

•  All about SQL scripts

The topics we cover today are pretty short. You should be able to run through this chapter very quickly.

What Is a Database?

A database is a logical container for related objects, such as tables, views, defaults, and procedures. Databases are given a certain amount of disk space, which the server manages. So, in addition to being a logical container for related objects and data, a database is also a very real physical container.

Every time you log into the server, you are given a database context.

A database is a logical container for related objects.

A database context is the database in which your connection exists at a particular moment. Every active connection (login) has a database context.

My favorite analogy for databases and database contexts is the glass house. The SQL Server is a big glass house, with many rooms. Each of those rooms is a database. The rooms hold lots of different objects, which are a part of that room. In a neat, orderly glass house, these objects are related to each other somehow—in the kitchen room (database) there are knives, cutting boards, dishwashers, and, of course, a kitchen table. In a sales database, there are salesmen tables, customer tables, and invoice tables, all related to one another.

Once you get past the front door (log in to the server), and until you leave the house (log out), you will always be in a room in the house. Whenever you are logged in to the server, you will always have a database context.

Current Context

Your current database context is listed on the ISQL/w window, if you’re using it. Figure 11.1 shows where you can find your current context.

Figure 11.1.  ISQL/w with the mouse pointer over the Select Database listbox.

Whether or not you’re using ISQL/w, you can always get your current context by executing a simple SQL function, DB_NAME().

select db_name()

---------------------
pubs

Finding the Database Context in an Application Program

You can always execute select db_name() to return a connection’s current context. Some programming languages will track the database context for you and keep a record of it on the client, so you don’t have to go to the server to ask. In Visual Basic’s Remote Data Objects, you must extract the current database from the Connection string, stored in rdoConnection.Connect. In DB-Library, you can run the function SQLChange$ (or dbchange() in C) to check if the database has changed.

Complete Object Names

Wherever you are, whatever your database context, you always have the ability to see objects in other databases. This is why the database analogy I like uses a glass house—the objects in other rooms are visible. Here is the complete name of the authors table:

pubs.dbo.authors

An object’s name is actually composed of three parts: the database, the owner, and the object name. In this example, the database is pubs, the owner is dbo, and the object name is authors.

When you reference an object in the current database, such as the authors table, the server knows what table you’re talking about because it assumes you mean “the authors table in this database.” The owner of a table is the user who created that table. There is a special user, the database owner, who typically owns all the objects in a database. This user is represented by the letters dbo.

If you do not explicitly specify a database, the current database is used to find the object. If you do not specify an owner, the server first looks for an object that belongs to you. If it doesn’t find one, it looks for an object owned by the dbo.

Most often, all of this stuff is useful only in finding the objects in other databases. Suppose you had another database, named oldpubs, with older information in it. If you wanted to select all the information from the oldpubs database, this query would do it:

select *
from       oldpubs..authors

The owner of the table is left out—the server looks in the oldpubs database for a table named authors belonging to you, or, failing that, belonging to the database owner.

Changing Database Contexts

After logging in to the server, you are placed in your default database context.

---

Tip:  
Your default database context can be changed by executing the sp_defaultdb system stored procedure. This changes my default data-base to pubs:

exec sp_defaultdb bmcewan, pubs

---

After logging in, if you want to change your context, use the USE command:

use master
go

Default database context changed to ‘master’

---

Note:  
On newer versions of ISQL/w, this message from the server is intercepted by the program and discarded, so you may have seen this output instead:

This command did not return data, and it did not return any rows

---

When you employ the USE command, you should execute it in its own batch. Batches are discussed in the second half of today’s lesson.

---

Note:  
In an application program, the messages indicating that the database context has changed are often more annoying than informative. I usually code my error and/or message handlers to ignore the 5701 server message.

ISQL/w ignores it, too: If you execute a USE in ISQL/w, you get back This command did not return data.

---