Synonyms in sql server

Synonym is nothing but it is an Alias or alternative name of the object. This creates abstraction between database object and the user.

You may think why we are creating the synonym let see the example. My full name is Bagesh Kumar Sing, my Nike name Bagi (in other word we can say that my alias or alternative name or synonym). When someone calls Bagi Means they are calling to me (Bagesh Kumar Singh). Now no need to call every time Bagesh Kumar Singh, for addressing me they call me Bagi.

In similar when we are taking to Sql Server, a full qualifier name is denoted in four parts

[Server Name].[Database Name].[Schema Name].[Database object Name]  this is too long right, then why not we create the alias of this object like myObj which refer the same. You may think what is the use of this. Suppose I have created one SP in Dev server and this SP has more than 1000 of line. When we will move this Sp to Prod server then it will not work because it is pointing the Dev server name. Here again we need to changes the Server name. It is tedious task. For overcoming this we can use synonyms. With the help of this we can create the synonym and it will reflect in sp. This is one simple example.

Creating Synonyms

There are 2 way to create the synonym

1.       Using T- script

2.       Using GUI

Using T-Script

use [AdventureWorks2012] create synonym MyPLPHistory for [DESKTOP-C3Q69P7\SQLEXPRESS].[AdventureWorks2012].[Production].[ProductListPriceHistory]

  

Now Synonym has been created. We can see this object in Synonym folder

  

We can use it on place of full qualifier name.

  

We will get same result.

Using GUI

Right click on the Synonyms folder and click on the New Synonyms

New Synonyms editor will be open

Here we need to write the Synonyms name, server name, Database name and schema name and click ok. A new synonym is created.

  

Now it is ready to use.

It can be very useful and can be created for Table, Views, Sp, functions, Local and Global Temp table etc.

Benefits of the Synonyms

• It  provides a layer of abstraction over the referenced object
• Allow changes to complicated (multi part) and lengthy names with a simplified alias as a same server resident object.
• Provides flexibility for changing the location of objects without changing existing code.
• It can be created in the same database to provide backward compatibility for older applications in case of drop or rename of objects.
• It can be useful if you give the front-end query tools like spreadsheets and Access linked tables direct links in to the tables.

Limitations of the Synonyms

• It is loosely bound to the referenced objects. So we can delete a SYNONYM without getting any warning that it is being referenced by any other database object.
• We can’t create SYNONYM of a SYNONYM.
• We can’t create a table with the same name of a synonym
• The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if we make any related error e.g. spelling error, the synonym will created, but we will get an error while accessing the object.
• SYNONYM can’t be referenced in a DDL statement