SYNONYM in SQL Server

A synonym is an identifier that can be used to refer an object from the data base that is having a very long name. Thus instead of using the long name every time we can have the alias or synonym. It gives us and extra layer of security as we have only the Abstract of the object in the form of name which helps us if the underlying object is changed or modified.

We can refer the synonym to perform operations on the base object. We can also use a synonym to refer objects on a linked server, thereby masking the server name.

• We can use a synonym to refer a base object in the following type of Query:
• SELECT, including sub queries
• INSERT
• UPDATE
• DELETE
• EXECUTE

There are some limitations while using synonym like we cannot use a synonym in a Data Definition Language (DDL) statement, such as an ALTER TABLE command. Synonyms are not schema bound; therefore, they cannot be referenced by schema bound objects like

Ø  CHECK constraints

Ø  Computed columns

Ø  Defaults

Ø  Rules

Ø  Schema-bound views and functions

To create a synonym,

USE UrDBName;
GO
CREATE SYNONYM MySyn

FOR UrDBName.ObjectNsame

GO

Use Synonyms :

USE test;
GO
SELECT * FROM MySyn;
GO







To drop a synonym, all you need to do is execute the DROP SYNONYM command followed by the synonym name or right-click the synonym and select delete from the context menu.

DROP SYNONYM [schema].SynonymName







You can always delete a synonym even if other objects are referencing it. we will not encounter an error until executing the object that references the synonym.

Hope this is useful in getting idea about the same. Please provide your valuable comment on the same.

Thanks

Anil Kumar Pandey

Microsoft MVP, Microsoft MCC,DNS MVM