CODEWITHSUNDEEP
sql-server-2008
Gold
Gold

Reputation: 62434

What is the use of SYNONYM?

What is the use of SYNONYM in SQL Server 2008?

Upvotes: 53

Views: 74653

Answers (7)

mnemonic
mnemonic

Reputation: 1645

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.

Have never required the first one but the second issue is rather helpful.

msdn is your friend

Upvotes: 4

cadaver
cadaver

Reputation: 47

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...

Upvotes: 4

Adriano Varoli Piazza
Adriano Varoli Piazza

Reputation: 7429

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.

Upvotes: 5

FistOfFury
FistOfFury

Reputation: 7145

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/

Upvotes: 72

Ramesh
Ramesh

Reputation: 1752

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.

Upvotes: 14

Hankman3000
Hankman3000

Reputation: 115

You can actually create a synonym in an empty database and refer it to an object in another database, and thus make it work as it should even though it is in a completely empty database (besides the synonym that you created of course).

Upvotes: 3

thomas
thomas

Reputation: 2642

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/

Upvotes: 5

Related Questions