Importance of where sprocs are stored 2

[tektipster79]

Are there any performance implications to storing a stored procedure in one database that accesses data in another database? Same server, just a different database. My reason for storing it on the other database is mainly for organizational purposes.

 

[Qik3Coder]

If you are doing cross db work then it depends on what you are doing. You probably won't notice a db hit unless you are going cross server.

If "for organizational purposes" is the only reason that you have it on db x instead of db y, then that's not really a legitimate reason for doing it. The databases should be as isolated as possible, otherwise there is no point in having separate databases, except for "organizational purposes, which we won't even go near.

The problem that you run into with cross db is if you ever change your database name. (Things get interesting)



If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[JoeAtWork]

Or what if you want to move the database to another server?

And how about the fact that your "full database backup" actually doesn't have the "full database"?

I would advise against this maintenance nightmare.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[tektipster79]

All great points, thanks guys!!