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 |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2002-07-30 : 17:04:55
|
| Hi.Please pardon my multi-part question.Does one lose any functionality (i.e. ability to run queries, complex joins, establish relationships, stored procedures, views, calculations on fields) when one is running SQL statements against multiple databases on a SQL Server instead of on one single database?I'm preparing to design a large database that seems to lend itself to being broken down into smaller dbs. However, I want to be able to offer my end users a fully web-enabled front end that they can use to search/update the databases any way they like. This would mean that I need to be able to create Views that span across multiple databases located on the same SQL Server 2000 machine. I notice there are various restrictions when using Views. Is there a limitation in this area?Thanks! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-30 : 17:38:14
|
quote:
Does one lose any functionality when one is running SQL statements against multiple databases on a SQL Server instead of on one single database?
ability to run queries - not always, but it doesn't improve eithercomplex joins - same as aboveestablish relationships - you can only declare foreign keys between tables in the same databasestored procedures - could be problematicviews - not reallycalculations on fields - depends if user-defined functions are involvedIf the information in various tables is in any way related or will be queried together, logically it should be kept in the same database. You should not separate them into different databases unless there are overwhelming issues that demand it...security, for example. Well, maybe not overwhelming, but pretty damn compelling at least.There are also performance considerations that benefit keeping everything in one database. You can keep all of the tables in filegroups to help improve performance, either all in one file/filegroups or distributed amongst multiple hard drives. You can't do this with separate databases because they cannot share filegroups, so there would be increased I/O and less chance for sequential data reading.Can you provide more information on exactly what kind of database you have, and how you plan on separating it? I could be wrong and it may truly be better to separate them, but we'll need more detail to know for sure. |
 |
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2002-07-31 : 09:46:50
|
| Rob,Thanks for the detailed answer. Essentially, this is a patient tracking database, so your point about data being "in any way related" is a good one because any data entered into the DB will be related to each unique patient record.I considered multiple databases because there are some "players" in the organization who are guarding their data very closely. It may be problematic to bring them on board at the pace I'd like to establish, so I considered adding them later via separate databases. I'm in the interview stage right now, charting existing data flow (which is a complete mess). I want to design one database and have the cooperation of all the players so I can knock it all out at once. I see now that I'll have to push some people to get in the game. I will not give up the features the single database offers, especially VIEWS, which I love!Cheers,Steelkilt |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-31 : 11:34:51
|
| Extra note for you... I was just running a few tests and the what not over here to see what all happens when running rather large queries across multiple databases (4 in total). The overhead a simple query put on the network was amazing. All 4 servers hit it like mad... and it was only a simple select/union statement (well, it was over 2 million records being selected though... small records but still). If you try something like this, you might want to moniter netwrok traffic for a bit. One other thing to watch for, permissions across databases get annoying really quickly.-----------------------Take my advice, I dare ya |
|
|
|
|
|
|
|
|
Views across multiple databases