What is the use of SYNONYM in SQL Server 2008?
7 Answers
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.
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.
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.
Related