How to make a query from multiple databases

Question

I have to join queries from multiple databases, something like:

SELECT T1.*, T2.* FROM DatabaseA.dbo.Table1 AS T1
INNER JOIN DatabaseB.dbo.Table2 AS T2 ON T1.ID = T2.ID

My questions:

How should I setup the connection string of System.Data.SqlClient.SqlConnection? Should I omit or leave empty the Initial Catalog? Should I set it to DatabaseA?
Is this a good way to perform this task? I'm thinking to prepare a third DB without tables but with a view with that SELECT (I can't put a view in the existing databases). Query usually is executed in 10/15 seconds but user has to wait so even few seconds less will be appreciated.

All DBs share the same login credentials.

Michael Fredrickson · Accepted Answer · 2012-03-19 15:38:55Z

2

This depends on the login within your connection string more than the Initial Catalog.

You can specify either database in your connection string, as long as the login that you're using has the permission required to query from both tables in both databases.

There's nothing inherently wrong with a query that spans multiple databases, so your proposed solution looks good to me.

Just be aware that if you're the one designing these databases, and determining which tables should live in which databases, that there is no referential integrity across databases, so ideally any tables that relate to each other should live in a single database.

answered Mar 19, 2012 at 15:38

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adriano Repetti Over a year ago

Yes, all DBs share the same credentials for login. Do you think it's better (both for performance and design) to use a third DB with a view? (query usually is executed in 10/15 seconds but user has to wait so even few seconds less will be appreciated).

Michael Fredrickson Over a year ago

I wouldn't suggest creating a third database to only contain a view... there's administrative overhead for every database that is created, so you should try to limit this as much as possible. I would instead try to get permission to create the view in either of the existing databases... however, unless you plan on indexing your view, I don't think it will improve the performance of your query.

Adriano Repetti Over a year ago

Unfortunately tables are related but law imposed to split them across multiple databases (I feel lucky they are at least on the same server). View will be indexed but I'll wait few months of feedback to tune it. Thank you, I guess that's all!

Oli · Accepted Answer · 2012-03-19 15:42:04Z

1

The connection string connects to the DB with the username's permissions, regardless of the database. If the user has access to execute the queries you will have no issues at all.

answered Mar 19, 2012 at 15:42

Oli

4271 gold badge7 silver badges21 bronze badges

Comments

Schiavini · Accepted Answer · 2012-03-19 15:44:32Z

1

The initial catalog will be the one you query without using DBNAME.dbo.TABLENAME. So if you choose DatabaseA as initial catalog, you can query like this:

SELECT T1.*, T2.*
FROM Table1 AS T1
INNER JOIN DatabaseB.dbo.Table2 AS T2 ON T1.ID = T2.ID

The initial catalog just means the default database.

If the login has access to both databases, it doesn't matter which one is the default when you use the full name.

Performance: 10/15 seconds sounds like a very long time. You should be able to improve the query and bring it down by using indexes or better query design. Making views won't improve the delay though. If you need some help with that, please provide more details.

answered Mar 19, 2012 at 15:44

Schiavini

2,9492 gold badges27 silver badges50 bronze badges

Collectives™ on Stack Overflow

How to make a query from multiple databases

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related