I’ve recently been working on an application which has to make use of data from within two databases (one onsite and one remote) with SQL generated to query the two databases along the lines of:

SELECT a.*, b.*
FROM [TableA] a
INNER JOIN [DatabaseB].[SchemaB].[TableB] b ON a.ID = b.ID

Referencing the second database using its full name is annoying to type but also limiting.  Imagine if you were working on a couple of projects, each of which used the same version DatabaseA, but which needed to reference different versions of DatabaseB.  If you were developing each of these databases in SQL Server you’d have to call the second version of DatabaseB something different which would end up breaking your SQL as it specifically references DatabaseB by name.

Synonyms are a neat way of overcoming this (plus they also sound like cinnamon which reminds me of bagels and who doesn’t like bagels? :)).  Basically what they allow you to do is create an equivalent name for a database object so you don’t have to reference it by its full name.

In the case of the example above I could create a synonym for TableB in DatabaseB as follows:

USE DatabaseA;
GO
CREATE SYNONYM DatabaseBTableB FOR [DatabaseB].[SchemaB].[TableB];
GO

Then use it:

USE DatabaseA;
GO
SELECT * FROM DatabaseBTableB
GO

And get rid of it:

USE DatabaseA;
GO;
DROP SYNONYM DatabaseBTableB;
GO;

Synonyms can’t be created directly against a database but can be created against the following objects:

• Assembly (CLR) Stored Procedures
• Assembly (CLR) Table Valued Functions
• Assembly (CLR) Scalar Functions
• Assembly Aggregate (CLR) Aggregate Functions
• Replication Filter Procedures
• Extended Stored Procedures
• SQL Scalar Functions
• SQL Table Valued Functions
• SQL Inline Table Valued Functions
• SQL Stored Procedures
• Views
• Tables

I think they’re really useful, not only when working with multiple databases but when it’s possible that the name of a database, view, table may change.  In that sort of scenario instead of having to update all your SQL you would just need to update your synonym.