polkadot (8/30/2014)

---

first, I'm going to tell you that there's a system admin guy on my team that seems to come up with junk statements and makes dubious claims. Recently, he told me that whenever joining on tables from different databases it is an 'Enterprise Best Practice' to join using linked server objects to make those joins even if the databases are hosted on the same server. (where linked servers don't exist, to create them, as well)

For example.

ServerAlpha has 5 databases:

DatabaseA

DatabaseB

DatabaseC

DatabaseD

And I need to build a query using tables from DatabaseA and DatabaseB.

Typically in those situations I just use

databasename.schemaname.tablename

....but he is suggesting instead I always use

linkedservername.databasename.schemaname.tablename

His reason: that you never know when the databases will be decoupled from the server and you don't want to have to go back into all your ssrs reports and edit the joins.

My thought about this is that the databases should be kept together given the high incidence of cross over joins AND it is just as possible for future database redesigns to involve merging two databases or revamping them so much that regardless of what server they're on, the query needs to be edited.

I believe this senior system admin made this statement only because I was wanting to join on two databases that in down-level didn't exist on the same server (they do in production) and didn't want to get involved in mirroring the production environment, but if you would please weigh in on this idea of ALWAYS using linked server objects when qualifying table joins, I'd appreciate it.