Synonyms in SQL Server

                                       SYNONYMS

                                                                                                                                               

* Synonym is a database object which contains the physical representation of data.

* Synonym is a short-cut name for the original base table .

* In case of DML operations both synonym and base table are vice versa .

* When we modify the structure of the base table the corresponding synonym also reflected with new changes.

Synonym will become invalid in 2 cases

1. When we drop the base table.

2. When we change the table name.

On invalid synonym we cannot perform any type of operations

Synonyms are classified into 2 types

Private Synonym:

Private synonyms are those synonyms which are accessed by only one particulate user and these are created by developer.

Public Synonyms:

Public synonyms are those synonyms which are accessed by more than one user and a normal database developer don't have the privileges to create. These are created and maintained by only DBA's.

Syntax:

CREATE SYNONYM <SYNONYM-NAME> FOR <TABLE-NAME>

Ex:

CREATE SYNONYM Emp FOR Employee

Syntax :

CREATE PUBLIC SYNONYM <SYNONYM-NAME> FOR <TABLE-NAME>

Syntax :

DROP SYNONYM <SYNONYM-NAME>

How to view all synonym names:

SELECT * FROM sys.synonyms