[dba-SQLServer] Creating joins across multiple databases

Arthur Fuller artful at rogers.com
Tue Apr 6 01:43:17 CDT 2004


Thanks for the vote of confidence, Francisco, but Robert is quite right.
Somehow in my copy/paste operation I screwed it up. All the egg belongs
on my face.

Meanwhile I'm giving some thought to Robert's objections to the strategy
of "related" dbs (which I freely admit I wouldn't do if the choice were
always mine, but sometimes it isn't).

Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
Francisco H Tapia
Sent: Thursday, April 01, 2004 1:00 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Creating joins across multiple databases


Knowing Arthur, i'm sure that was a publisher's err, not an author err, 
from posting to HTML...


Djabarov, Robert said the following on 4/1/2004 12:43 PM:

>...He's consistently missing schema owner...Just an observation ;)
>
>Robert Djabarov
>SQL Server & UDB
>Sr. SQL Server Administrator
>Phone: (210)  913-3148
>Pager: (210) 753-3148
>9800 Fredericksburg Rd. San Antonio, TX  78288
>www.usaa.com
>
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of 
>Francisco H Tapia
>Sent: Thursday, April 01, 2004 2:41 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] Creating joins across multiple databases
>
>
>copy paste...
>Djabarov, Robert said the following on 4/1/2004 10:51 AM:
>
>  
>
>>Based on what I've seen so far, it's not a good idea to do cross-db
>>joins, and not only because of security issues as Francisco mentions, 
>>but also because of questions like "Who the heck is this guy and what 
>>is he doing in my swamp?" which immediately start popping up, which 
>>leads to finger-pointing when there is a slow-down or outage.  It also

>>violates database designation (DSS, OLTP) when it's OLTP for a native 
>>user while becoming a DSS for the new-comer.
>>
>>BTW, Francisco, did you copy the article or retyped it?  If you copied
>>it, I see a minor problem with Author's syntax...
>>
>>
>>Robert Djabarov
>>SQL Server & UDB
>>Sr. SQL Server Administrator
>>Phone: (210)  913-3148
>>Pager: (210) 753-3148
>>9800 Fredericksburg Rd. San Antonio, TX  78288
>>www.usaa.com
>>
>>-----Original Message-----
>>From: dba-sqlserver-bounces at databaseadvisors.com
>>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
>>Francisco H Tapia
>>Sent: Thursday, April 01, 2004 12:41 PM
>>To: dba-SQLServer at databaseadvisors.com
>>Subject: [dba-SQLServer] Creating joins across multiple databases
>>
>>I'm including the latest article from Arthur Fuller on the Builder.com

>>SQL Newsletter because I hope it can bring forward some positive 
>>discussion.  Plus I'm sure many of us have dealt with this particular 
>>situation or will deal with it in the future.
>>
>>/<BUILDER.COM SQL Newsletter>/*
>>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./ /<BUILDER.COM SQL Newsletter>/
>>
>>Yes it is easy enough to join accross multiple databases and even 
>>accross several Sql Server linked servers :)   (very cool)... however
>>    
>>
>my
>  
>
>>concern and one that was not addressed in this article is the security

>>aspect of it... say you just have a user who calls the report... does 
>>the user need to use an application who's using an application role 
>>and
>>    
>>
>
>  
>
>>thus gets better than normal priviladges on the server?  I have not
>>    
>>
>been
>  
>
>>successful in adding new users to another database w/o also having to 
>>takes some hard steps; my solution was to ... Create VIEWS on the
>>    
>>
>target
>  
>
>>database... add the user to that 2nd database, then give explicit 
>>selet crights on the views and then join accross from db2 to db1 via 
>>the
>>    
>>
>sproc
>  
>
>>using the views instead of the tables... so while the data is not 
>>duplicated, the user is, and now has rights to base views that
>>    
>>
>represent
>  
>
>>tables :( , so because I think I might be doing this wrong.. I'm 
>>hoping Arthur or someone else will chime in with a few clues :)
>>
>>Thanks,
>>
>> 
>>
>>    
>>
>
>
>  
>


-- 
-Francisco


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com




More information about the dba-SQLServer mailing list