OK Synonyms are not new in SQL, they were introduced back with SQL Server 2005, but to date I’ve never got round to using them until now.
A synonym is really just a shortcut reference to a object (Table, View, Stored Procedure or Function) that resides in a database on another server that you have defined as a Linked Server (Note: You can also create them for object in databases on the same server and in the same database).
Where I have used Linked Server quite a bit before my queries become quite long and complicated with the long reference to the tables
SELECT * FROM linked_server.database.schema.table|view
This can be a problem when trying to deploy projects where the Linked Server name might change, as the production environment differs from my development environment, and I will have to re-touch all my code on the production environment (eek).
With using a synonym for each object I call on the Linked Server I can code my queries using the synonym rather than the long reference.
SELECT * FROM [synonym name]
Definition from SQL Server BO:
A synonym is an alternative name for a schema-scoped object. Client applications can use a single-part name to reference a base object by using a synonym instead of using a two-part, three-part, or four-part name to reference the base object.
This means, that when I deploy my code to a different environment, I will have to drop and recreate the Linked Server and any Synonyms but I do not have to touch any other code (jobs-a-good’n).
Note: There is no Alter feature for changing Synonyms (or Linked Servers) but TBH that’s not a biggie!
Note: IN SQL Server 2008 you will loose your IntelliSense, but I think you don’t get it with Linked Servers anyway.
To create a Synonym for a remote object on a linked server you can use the SSMS but it is as easy to script it.
CREATE SYNONYM synonym_schema.synonym_name FOR linked_server.database.schema.table|view
Look for “synonyms [SQL Server]” in SQL Server Books Online for more information.