advantages of synonyms IN SQL?

why synonyms are used?,advantages of syNONYMS IN开发者_开发百科 SQL?

They're just shorthand names for objects inside a database. For example, you can create a synonym called Products if you have a namespace'd table in a database called ProductionControl.Inventory.Products. They're also handy for controlling named access to other databases in stored procedures. If you have SPs that refer to tables in other databases, creating a synonym and using that instead gives you more control in case the target of the synonym ever changes. This is useful in scenarios where you have SPs that refer to a development database, but when you deploy to production the name is different. So you'd just update the synonym and you'd be OK.

From MSDN Understanding Synonyms

A synonym is a database object that serves the following purposes:

• Provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.

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

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

Synonyms can help you decouple the name and location of the underlying object from your SQL code. That way you can code against a synonym table even if the table you want 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 would impact 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 underlying object changes location or name, I only need to update my synonym to point to the new object.

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