Difference between a linked server and a synonym?

Question

Is there a difference between t-sql linked server and a synonym? If so what are they, and in which cases would I choose one over the other?

Answer 1

Linked Servers and Synonyms are used for different purposes in SQL Server. One is not a substitute for another.

Linked Server is a way of establishing a connection to an external data source which could be another SQL Server, Oracle or any other type of data that has an OLE DB provider. More info here.

Synonyms are way of creating alias for database objects so as to simplify names in SQL queries and to provide a layer of abstraction to reduce impact on client queries when referenced object changes name or location. More info here.

Let us say you are on SQL Server ABC and would like to create a stored procedure that needs to access ProductCategory table in Adventureworks database on SQL Server XYZ.

1. You would first create a linked server with some name - usually same name as target - XYZ

2. You can now access the table as follows

   SELECT * FROM XYZ.dbo.Adventureworks.ProductCategory;

   Note: You can use the above 4-part name in your queries and stored procedures to access not just 'ProductCategory' but any other tables and views.

3. Such long names complicates your queries and if you have to point to a different server or table, you would have to change all the queries.

4. Instead, you could create a synonym that references above remote database object and it can have short name, say ProductCategoryRemote and then use it in your queries as

   SELECT * FROM ProductCategoryRemote;

   If you decide to use a different table or server (like when moving from UAT to PROD environments), all you need to do is drop and recreate the synonym referencing the new object.

   Note: Synonym can reference objects within the same database, other databases on the same server or another server through linked server as in this example.

In conclusion, Linked Server are required to access an external data source and synonyms are used to create alias for database objects.