This applies to a SQL Server 2012 instance. We are considering structuring things to where we have a database that contains our source data, and then separate database (on the same instance) that would contain any stored procedures that query that data. The stored procedures would then explicitly reference the "data" database when pulling any data.
Would there be any query performance problems in structuring things this way?
Edit: I'm coming at this from a developer's perspective; this is being proposed from the DBA level and I couldn't tell you what might be behind it. Best I can tell it's some sort of separation of interests – the data database is loading data from a 3rd party app so we can more easily report on it. I think they want to separate out stored procs we create to not clutter the "source data" db. This db is not a vendor database per se, but the application's data is unavailable in its native format. So there are daily processes that import the vendor data via ODBC into SQL Server so we can more easily get at it.
Best Answer
I've seen this type of thing done before. Particularly where the data & possibly some code is coming from an outside location and may be overwritten with restores (for example). It also allows the code to be backed up separately from the data (yes I know you could do that with filegroups also).
In general as long as your code is on the same instance as the data then there are no performance issues.
However you will likely run into some permissions issues. There is something called ownership chaining that allows someone to successfully execute a stored procedure that updates a table even though they don't have permissions to the table directly. This does not work cross-database.
Unless of course you turn on something called cross database ownership chaining. This is not turned on initially and there are of course security implications of turning it on. It basically allows for exactly what it says. Ownership chaining will work across databases. It can be turned on at a server or database level, although it is highly recommended that you not turn it on at a server level unless required for some reason. You can however turn it on for your two databases and you should be fine.
I recommend reading the links above. They appear to be quite good and will give you a better understanding of what I'm talking about.