Advantages of synonyms IN SQL?

Question

Why are synonyms used ?, benefits of syNONYMS IN SQL?

Answer 1

These are simply the abbreviated names of the objects inside the database. For example, you can create a synonym named Products if you have a namespace'd table in a database called ProductionControl.Inventory.Products . They are also convenient for managing named access to other databases in stored procedures. If you have SPs that reference tables in other databases, creating a synonym and using it instead gives you more control if the purpose of the synonym changes. This is useful in scenarios where you have SPs that relate to the development database, but the name is different when deployed to production. So you just updated the synonym, and everything will be fine.

Answer 2

From MSDN Understanding Synonyms

A synonym is a database object that performs the following tasks:

• Provides an alternate name for another database object, like a base object that can exist on a local or remote server.

• Provides an abstraction layer that protects the client application from changes made to the name or location of the underlying object.

Answer 3

On some enterprise systems, you may have to deal with remote objects that you have no control over. For example, a database maintained by another department or team.

Synonyms can help you separate the name and location of the base object from your SQL code. This way you can encode the synonym table, even if the desired table is moved to a new server / database or renamed.

For example, I could write a query like this:

 insert into MyTable (...) select ... from remoteServer.remoteDatabase.dbo.Employee 

but then if the server or database, schema or table changes, it will affect my code. Instead, I can create a synonym for the remote server and use the synonym instead:

 insert into MyTable (...) select ... from EmployeeSynonym 

If the base object changes location or name, I only need to update the synonym to point to a new object.

http://www.mssqltips.com/sqlservertip/1820/use-synonyms-to-abstract-the-location-of-sql-server-database-objects/