General design efficiency question

My general question is whether there is anything to be gained by having 50 tables in one database versus 5 tables each in 10 databases.

I have a number of different databases running on a server (SQL Server 2k). The different databases represent different functional groups, for instance car maintenance, cab reservation/dispatch, cab accounting, limo reservation/dispatch, limo accounting, etc. There is some crossover, for instance the cab dispatch system would look to car maintenance to validate the car number entered.

A friend who happens to be IT Director at the local university suggested that the server would run more efficiently if there was only one database, rather than the roughly 12 I have now. His belief is that each separate database carries a certain amount of overhead, and combining them into one would be advantageous.

Is he all wet, or would there be gains to be made?

TIAThere would be gains. It is just a matter of whether you would notice them. I have never tested the scenario, so I have no actual numbers. Personally, I would favor the single database approach, for permissions administration reasons. A user only needs permissions on a stored procedure and not the underlying tables, IF all of the tables it accesses are in the same database. Same for views.

Now, suppose you want to restore the 10 databases back to a point in time just before your temp deleted a pile of data in "some tables". Do you want to do 10 separate restores, one?

I am sure some of the other folks here can come up with other examples, if they tried.|||He's right for the wrong reasons.

I don't think you would get a performance boost from combining the databases. You could make arguments for either increased efficiency or decreased efficiency either way.

But...from a data management standpoint it makes administrative sense to combine the databases if they reference eachother for lookup values or cross-database queries. From you limited description of the situation, I would recommend combining them.|||Thanks for your thoughts. I guess I'll look at going through the effort.|||I read the first line and fell off my barstoo...um office chair

My general question is whether there is anything to be gained by having 50 tables in one database versus 5 tables each in 10 databases.

Are you kidding? Ever hear of maintenance?

I have a number of different databases running on a server (SQL Server 2k). The different databases represent different functional groups, for instance car maintenance, cab reservation/dispatch, cab accounting, limo reservation/dispatch, limo accounting, etc. There is some crossover, for instance the cab dispatch system would look to car maintenance to validate the car number entered.

A friend who happens to be IT Director at the local university suggested that the server would run more efficiently if there was only one database, rather than the roughly 12 I have now. His belief is that each separate database carries a certain amount of overhead, and combining them into one would be advantageous.

Is he all wet, or would there be gains to be made?

TIA

I like the cut of his gib...are all the tables named differently? Also, what about the apps? Would they be hard to port?|||Putting objects in different databases will allow you more flexibility in terms of allocating data and log files. (IE if you have to use primary for select into reasons, you can more easily manage it with multiple databases). Also, depending on your backup requirements you may be able to set some databases to simple mode, some to full, only do trn backups for certain databases and the list goes on. I think it's more a management thing then a performance thing.

-kilka|||Putting objects in different databases will allow you more flexibility in terms of allocating data and log files. (IE if you have to use primary for select into reasons, you can more easily manage it with multiple databases). Also, depending on your backup requirements you may be able to set some databases to simple mode, some to full, only do trn backups for certain databases and the list goes on. I think it's more a management thing then a performance thing.

-kilka

Nope ... and thank you for playing (at least for the first statement). You can create as many segments as you desire for a single database for both data and log files ... non-clustered indexes on multiple disks, split log files, etc., etc., etc.

Not to mention that referential integrity rules can only be enforced with database, not across database.

Also, IMHO, cross database joins requires the engine to drill down thru the metadata of the other databases to access the index and page structures of the "foreign" database since SQLServer "cooks" it database space when it is allocated.

That being said, it would be interesting to see the results of an empirical test. If Paul Randal is still hanging around, maybe he can comment on this topic!|||What do you mean by "cooks" space when it's allocated?