Synonyms in SQL Server

In SQL Server, the synonym is the database object that provides alternate name (alias) to another database objects such as table, view, stored procedure, etc. in the local server or a remote server. It provides a layer of abstraction and protects the client application in case of a name change or location change made to the base object.

For example, the local MyDBServer contains the HR database that includes the Employee table. Now, the remote client application has to refer to this table with full name like MyDBServer.HR.dbo.Employee. In this scenario, you can create a Synonym for the Employee table which can be used in the client application.

Create Synonym

A few points to consider while creating a synonym:

A synonym must have a unique name just like other database objects in a schema.
A synonym cannot be a base object for another synonym.
A synonym cannot reference a user -defined aggregate function.

Use CREATE SYNONYM in T-SQL to create a new synonym in SQL Server.

Syntax:

CREATE SYNONYM [schema_name.] synonym_name FOR object

To create a new synonym in the HR database, open and login to the SQL Server Management Studio and select New Query. Copy the following query to the query window and execute.

Example: Create a Synonym

CREATE SYNONYM Emp FOR dbo.Employee;

Here Emp is the synonym name and Employee is the database table for which a synonym is created.

The new synonym is created under the Synonym folder of the HR database, as shown below.

You can now reference the Employee table using the synonym Emp. Let's insert data using Emp synonym. The following shows the data in the Employee table.

Now, use the synonym Emp to insert values into the Employee table, as shown below.

Example: Insert Data using Synonym

INSERT INTO Emp
values('Amit', 'Saha', '[email protected]', '22545678',  '10/03/2017',  2, 67000, 20);

Now, let's check if the new row is inserted or not, as shown below.

To view all the synonyms, use the following SELECT statement:

Example: Get All Synonyms

SELECT name, base_object_name, type
FROM sys.synonyms;

Alter Synonyms

You cannot alter a synonym. To make changes, you first have to drop the synonym and re-create it.

Delete Synonyms

Use the DROP SYNONYM statement to delete a synonym.

The following deletes the Emp synonym.

Example: Delete a Synonym

DROP SYNONYM Emp;