SQL Server developers sometimes have to use tables residing in a database other than a project's "home" database. This need typically arises when you need to draw information from some other application; for instance, you may want to be able to SELECT from one or more tables residing in foreign databases that you seldom, if ever, update.

In this tip, I'll walk you through two methods for developing in multiple databases.

Approach #1

Let's say there's an accounting application that stores the names of your firm's clients. Your project is to replace the rather primitive order-entry system in that database with a custom database that's more geared to deal with the specific requirements of your firm. (For example, travel agencies have specific needs, such as, the creation of detailed itineraries which might include flight and hotel information.)

Weekly SQL tips in your inbox

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!

You might assume the existence of any given customer in the accounting application, but there is no point in duplicating this information. The specific client information will be local to the order-entry database. You do, however, need to reference the customer table in the accounting application.

To use a table in another database, you fully specify its name, like this:

SELECT * FROM Accounting.dbo.Customers WHERE CustomerID = 12345

Using this syntax, you can refer to any table in any foreign database. If the foreign database resides on another server, you can fully extend the table name by including a prefix indicating the server itself, like this:

SELECT * FROM Server1.Accounting.dbo.Customers WHERE CustomerID = 12345

This syntax requires that Server1 is registered in your list of linked servers; however, you can refer to any table in any database on any server. (Note that you must also have permission to access this server and database.)

Approach #2

There are several reasons why you might want to break the single database you are developing into multiple databases, including:

* Subject areas: In a rich database (i.e., a database that is comprised of many tables), it may make sense to create a separate database for each subject area. In this context, a subject area is understood as a subset of the logical database--the users of which have no interest in the other subject areas.

* Maintenance applications: A given application X may only address some of the tables in the logical database; therefore, it may be prudent to isolate those tables in a separate physical database.

* DML approach: Certain tables (e.g., the ISO list of country codes) may be administered by hand or by a separate application designed solely to administer these lookup tables. In that case, it might be best for this information to reside in a separate database. One of my favorite uses of this approach is to isolate countries, regions (e.g., states, provinces), and cities in a separate database so that all other databases in the enterprise can take advantage of this information without duplicating it.

The penalty you pay to refer to another database on the same server is negligible. Since the connection is open, there's very little impact if you want to refer to a database. You can create views or stored procedures or UDFs that include the references to foreign tables, and above that level no author of subsequent objects needs to know that some of the objects reside on other servers and/or in other databases.