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.
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.
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.
CREATE SYNONYM Emp FOR dbo.Employee;
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
Now, use the synonym
Emp to insert values into the
Employee table, as shown below.
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:
SELECT name, base_object_name, type FROM sys.synonyms;
You cannot alter a synonym. To make changes, you first have to drop the synonym and re-create it.
DROP SYNONYM statement to delete a synonym.
The following deletes the
DROP SYNONYM Emp;