If you've used SQL Server for any length of time, you've probably created multiple databases across different servers and used views and fully qualified table names to aggregate data, and you've probably discovered the problems with doing this when you've got 10 conjoined databases that you need to deploy multiple versions of on the same test server?

For example, you have 2 databases on your server. One, a customer repository, has tables relating to customer details and the other, a common database with countries, tax codes and such like. A view in your customers repository might pull together information about a customer and her country as follows;

SELECT cu.ID, cu.Name, co.CountryName FROM Customer cu
INNER JOIN CommonDatabase.dbo.Countries co ON co.ID=cu.CountryID

Now, the problem exists when you need to have customers v1.0 (with commons v1.0) alongside customers v2.0 (with commons v3.0), on the same server. As you can only have one database named CommonDatabase, this can't be done without renaming all the references above.

Enter the synonym. A synonym is an alias in your source database that references a target elsewhere in the SQL universe. This lets you write your views etc against the synonym, then point the synonym at any table, in any database, on any server.

CREATE SYNONYM Countries FOR CommonDatabase.dbo.Countries

This synonym points to the countries table in the common database, and you write your views against the synonyms rather than the fully qualified table name:

SELECT cu.ID, cu.Name, co.CountryName FROM Customer cu
INNER JOIN Countries co ON co.ID=cu.CountryID

Now, when you need to deploy multiple versions of the same databases, you just change all your synonyms to point to the different locations.

In addition to tables, synonyms can also point to views, stored procedures, and functions.