One takeaway I had from your post is that you use tables in another DB and reference them in the main DB via synonym. We have a web server that references our software DB in this manner and I always wondered if speed would be impacted by such a model (I've never tested it mainly because I'm not the developer) but it seems to be performing well all of these years. After reading your post I find myself thinking, "It can't be too bad if Jeff Moden uses it!" Any caveats you can think of for this model? Keep in mind, we have a 5GB database and a very low amount of web traffic (we're a small member-based company, not a Fortune 500).
We use synonyms a lot between databases so we can keep the general functionality separate and give us lines of demarcation for all sorts of things including the isolation of large tables and similar things. One caveat that we've run into in the past is similar to what we run into with views. We don't generally use "Hungarian Notation" to identify different objects and so it sometimes takes people a second or two to figure out if something is a table, view, function, or synonym. But it takes virtually no time to do such a thing.
Another more important caveat is that you can't use DRI (FKs) between tables that live in different databases. This hasn't been a problem for me because I've only split of the largest of tables and all of those are some form of audit or history or transaction table that has no FKs. If I did have such a need, I'd simply make a copy of the usually very small PK table. If you updated one of the copies, you'd have to update them all. Extended properties on the tables are a good thing to remind folks and we do use them. If that doesn't work for you, you could always make a "do nothing" function that has schema_binding on the table with a note in the flower box to remind people.
An example of why we don't use any form of "Hungarian Notation" is that today's table could become tomorrows view or synonym and vice versa. If you think not using "Hungarian Notation" is confusing, just wait until you run into a view with a "tbl" prefix or a synonym with a "tbl" prefix.
There is another suggestion that I have that has also helped us a lot. If you move a table to another database, always leave an empty copy of the table in the original database (of course, it will be differently named than the original because the synonym will use the original name to keep from having to change code). In a pinch, that allows the database to be copied without having to copy the database with the large table in it. This works particularly well for audit tables, which usually turn out to be the largest tables in most databases and are usually not essential if you need to "get back in business" after a massive crash or when you need to make a copy of the database for Development or Test purposes. All you need to do is rebuild the synonym(s) to point at the local table(s).
Also, I've not done specific performance testing for the differences between hitting a local table in the same database or a table in a different database on the same instance, but I've never found a difference during performance tests of related stored procedures affected by such a change.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)