Presents your 
SQL SERVER E-NEWSLETTER for March 30, 2004

<------------------------------------------->
 
Creating joins across multiple databases
 
Enterprise databases often comprise several databases--one from 
production, another from marketing, and so on. All of the databases 
rely on a certain amount of common data, which is frequently copied to 
each database that needs it. 
 
However, this copying routine creates a new problem: skew. Where there 
is duplication, there is at least the opportunity for skew. The 
solution to this problem is often replication, or scheduled jobs that 
update each copy of the common data from one "master" database. This 
approach works, but sometimes a better method is to move all the common 
data into one database, and then access it as needed from the other 
databases. This eliminates both the skew and the replication jobs. 
 
This approach typically requires joining some local tables to the 
common tables. For example, suppose you have two databases: Common and 
Sales. The Customers table resides in the Common database, while the 
Orders table resides in the Sales database. You want to create a query 
that lists the Customer information along with the Order information. 
How do you join these two tables? The answer lies in prefixes. 
 
SQL Server uses a dot-delimited nomenclature that extends outward to 
the database and the server. To specify a column in a table in another 
database, name the other database: 
 
SELECT CustomerID FROM Common.Customers 
 
To join a table from the Common database to a table in the current 
(Sales) database, use this same nomenclature: 
 
SELECT * FROM Common.Customers 
INNER JOIN Orders 
ON Common.Customers.CustomerID = Orders.CustomerID 
 
Not every organization's application domain requires multiple 
databases. If your organization does require multiple databases, it's 
easy to create joins across databases. 
 
Arthur Fuller has been developing database applications for more than 
20 years. He frequently works with Access ADPs, Microsoft SQL 2000, 
MySQL, and .NET. 
 
----------------------------------------