What is the use of SYNONYM?

Solution 1:

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/

Solution 2:

Synonyms provide a great layer of abstraction, allowing us to use friendly and/or local names for verbosely named or remote tables, views, procedures and functions.

For Example

Consider you have the server1 and dbschema as ABC and table name as Employee and now you need to access the Employee table in your Server2 to perform a query operation.

So you have to use like Server1.ABC.Employee it exposes everything ServerName,SchemaName and TableName.

Instead of this you can create a synonym link Create Synonym EmpTable for Server1.ABC.Employee

So you can access like Select * from Peoples p1 inner join EmpTable emp where emp.Id=p1.ID

So it gives the advantages of Abstraction, Ease of change,scalability.

Later on if you want to change Servername or Schema or tablename, just you have to change the synonym alone and there is no need for you do search all and replace them.

If you used it than you will feel the real advantage of synonym. It can also combine with linked server and provide more advantages for developers.

Solution 3:

An example of the usefulness of this might be if you had a stored procedure on a Users database that needed to access a Clients table on another production server. Assuming you created the stored procedure in the database Users, you might want to set up a synonym such as the following: USE Users; GO CREATE SYNONYM Clients FOR Offsite01.Production.dbo.Clients; GO

Now when writing the stored procedure instead of having to write out that entire alias every time you accessed the table you can just use the alias Clients. Furthermore, if you ever change the location or the name of the production database location all you need to do is modify one synonym instead of having to modify all of the stored procedures which reference the old server.

From: http://blog.sqlauthority.com/2008/01/07/sql-server-2005-introduction-and-explanation-to-synonym-helpful-t-sql-feature-for-developer/

Solution 4:

Seems (from here) to create an alias for another table, so that you can refer to it easily. Like as

select * from table longname as ln

but permanent and pervasive.

Edit: works for user-defined functions, local and remote objects, not only tables.

Solution 5:

I've been a long-time Oracle developer and making the jump to SQL Server.

But, another great use for synonyms is during the development cycle. If you have multiple developers modifying the same schema, you can use a synonym to point to your own schema rather than modifying the "production" table directly. That allows you to do your thing and other developers will not be impacted while you are making modifications and debugging.

I am glad to see these in SQL Server 2008...