Chris Harshman (6/24/2016)
bob.probst (9/11/2014)
I use synonyms to refer to any object outside of the database that I'm working in. They make for a useful way to encapsulate an object that I otherwise have no control over. If a database moves or a foreign object gets renamed, I just update synonyms and move on. They are also very useful for moving between dev and prod environments where external objects may be in different locations.In our environment, the database names have a suffix for different environments such as _DEV, _QA, _UA, _PROD. I've found synonyms to be the easiest way for the developers to manage their queries that use multiple databases, so they're not putting hardcoded database names in their stored procs. Makes it easier to promote their code because then they don't have to change everything querying xxx_DEV database when it's time for testing or rollout.
Amen to that! Same thing here. Without synonyms, it would take forever and be an incredibly error prone process. To wit, we don't allow anything more than the 2 part naming convention in any of our code and it has been an absolute godsend. In many cases, it's also allowed us to keep databases up an running massive refreshes of data occur even in production databases. While using the "Set A" version of tables, we can create and populate "Set B". Once "Set B" has been loaded and completely validated, we just repoint the synonyms. Total "downtime" for even an hours-long process is less than a couple dozen milliseconds. It doesn't break anything if the synonym is in use, either. The system will wait (be thoughtfully blocked for the drop) until it's not being used and then the drop just continues with no fanfare or errors.
--Jeff Moden
Change is inevitable... Change for the better is not.