SQL Synonym

SQL Synonym, it is a way to set an alias to an already existing or potential object [Table, View, Stored Procedure, Function]. It is just a pointer or reference, so it is not considered to be an object. DML commands/operations [Insert/Update/Delete] can be applied on Synonym if the Synonym is created for table.

If you have to create a synonym, you need to have CREATE SYNONYM permissions. If you are a sysadmin or db_owner you will have these privileges or you can grant this permission to other users. Also, if you create a synonym that crosses databases you will need permissions for the other database as well.

 

CREATE Synonym

CREATE Synonym statement is used to create a new Synonym. Here is the Syntax for creating a Synonym.

CREATE  SYNONYM "SynonymName"

CREATE  SYNONYM [DatabaseName].[SchemaName].[SynonymName]

-- Create Department Synonym with necessary constraints

EXAMPLE1:

CREATE SYNONYM [dbo].[Syn_Department] FOR [Department]

EXAMPLE2:

CREATE SYNONYM [dbo].[Syn_Department] FOR [Payroll].[dbo].[Department]

EXAMPLE3:

IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = N'Syn_Department')

CREATE SYNONYM [dbo].[Syn_Department] FOR [Payroll].[dbo].[Department]

GO

DROP Synonym 

Example for deleting the existing Synonym using the DROP SYNONYM command is given below.

EXAMPLE1:

DROP SYNONYM [dbo].[Syn_Department]

EXAMPLE2:

IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N'Syn_Department')

DROP SYNONYM [dbo].[Syn_Department]

GO

RENAME Synonym 

Example for renaming the existing Synonym is given below:

EXAMPLE1:

EXEC sp_rename 'Syn_Department', 'Syn_Department_Test'

Another way of renaming the existing Synonym is, DROP the existing SYNONYM and CREATE the new Synonym is given below.

EXAMPLE1:

DROP SYNONYM [dbo].[Syn_Department]

CREATE SYNONYM [dbo].[Syn_Department] FOR [Payroll].[dbo].[Department]

EXAMPLE2:

IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = N'Syn_Department')

DROP SYNONYM [dbo].[Syn_Department]

GO

CREATE SYNONYM [dbo].[Syn_Department] FOR [Payroll].[dbo].[Department]

SELECT Synonym

Retrieving the existing Synonym data using the SELECT command is given below. This command retrieves the data from Department table since this synonym is created by using the Department table object. Selecting the Synonym consists of.

EXAMPLE:

SELECT * FROM [dbo].[Syn_Department]

UPDATE Synonym 

Updating the record of table using Synonym. This update row(s) would be affected on Department table since this synonym is created by using the Department table object.

EXAMPLE:

UPDATE Syn_Department SET DepartmentName = 'Computer Science'

WHERE DepartmentName = 'Computer'

DELETE Synonym 

Deleting the record of table using Synonym. This delete command would be affected on Department table since this synonym is created by using the Department table object.

EXAMPLE:

DELETE FROM Syn_Department WHERE DepartmentName = 'Computer'