Understanding Synonyms in SQL Server

What are Synonyms?

A synonym is a database object that provides an alternative name for another database object, known as the base object, which can be located on either a local or remote server.

Why Synonyms?

• Simplification: They simplify the SQL queries by providing shorter or more meaningful names for complex object names.
• Abstraction: Synonyms hide the details of the underlying database objects, allowing changes to those objects without affecting the code that references the synonyms.
• Flexibility: They enable you to change the database schema without having to rewrite existing SQL code.

Syntax

CREATE SYNONYM schema_name.synonym_name FOR [object]

Example. Create a Synonym for a local object.

I have a Sales. Customer table in the AdventureWorks2022 database. Now, I am going to create a synonym in the MyWork database with the name dbo.SalesCustomer.

--Customer table in AdventureWorks2022
SELECT * FROM AdventureWorks2022.Sales.Customer

--Create a synonym for the Customer table MyWork databasel
USE MyWork
CREATE SYNONYM dbo.SalesCustomer
FOR AdventureWorks2022.Sales.Customer

--Query synonym to access the Sales.Customer base table
SELECT * FROM dbo.SalesCustomer

Output

Example. Create a Synonym for a remote object.

In this example, I have the AdventureWorks 2022.Person.[Address] table on the MyDevServer linked server. Now, I am going to create a synonym named dbo.PersonAddress.

CREATE SYNONYM dbo.PersonAddress FOR MyDevServer.AdventureWorks2022.Person.[Address]

What operations can we do using Synonyms?

The following operations can be performed using synonyms.

• SELECT
• UPDATE
• EXECUTE
• INSERT
• DELETE
• SUB-SELECTS

Get information about synonyms

The catalog view contains an entry for each synonym in a given database.

SELECT * FROM sys.synonyms

Output

Conclusion

By creating synonyms, we can use more intuitive and shorter names for complex or remote database objects, which makes your SQL code easier to maintain and understand.