![clock](../themes/indigo/img/timeicon.gif)
July 8, 2024 12:17 by
Peter
Synonyms: What Are They?
A synonym is a type of database object that gives another database object, called the base object, a different name. The base object might be on a local or remote server.
Why Use Synonyms?
- Simplifying: They make the SQL queries simpler by giving complex object names shorter or more meaningful names.
- Abstraction: By hiding the specifics of the underlying database objects, synonyms enable modifications to those items without impacting the code that uses them as references.
- Flexibility: You can modify the database structure using them without needing to redo the current 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
![](/image.axd?picture=2024%2f7%2fOutput-1.jpg)
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
![](/image.axd?picture=2024%2f7%2fResults-1.jpg)
Conclusion
Synonyms allow us to utilize shorter, more understandable names for complex or remote database objects, which simplifies and maintains your SQL code.
HostForLIFEASP.NET SQL Server 2022 Hosting
![](https://europeanwindowshosting.hostforlife.eu/image.axd?picture=2015%2f4%2fhostforlife+banner.png)