Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-06-09 : 08:24:06
|
| David writes "I'm using SQL2000 on Windows 2K Server. How easy, if possible at all, is it to access data between databases on the same SQL server? I've already created a web application that uses Database1, which contains a table named tblEmployees. I'm now creating another web application that needs to access this tblEmployees table and I certainly don't want to have two employee tables that have to be maintained separately. Now I'm wondering: What criteria is considered when deciding whether to use one or multiple databases for a solution? Thanks." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-09 : 08:28:32
|
| It's easy to access the data, just include the database name when you reference the table:SELECT * FROM database1.dbo.tblEmployeesYou can also join tables across databases:SELECT A.Name, B.CompanyName FROM database1.dbo.tblEmployees A INNER JOIN tblCompanies B ON A.CompanyID=B.CompanyIDThe only problem with multiple databases is that you cannot enforce referential integrity and foreign keys across them. You can however write triggers to manage that integrity. There's no hard and fast rule for when to split apps into different databases, except where security and data separation are paramount concerns or requirements. Otherwise you'd have to analyze the strengths and weaknesses of each scenario and probably test them to know which is better. |
 |
|
|
|
|
|
Sharing data across databases