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
