Updated March 10, 2023
Introduction to SQL synonyms
The following article provides an outline for SQL synonyms. A synonym in standard query language(SQL) is an identifier that is used to reference another object in the local or remote database server. It basically provides another short and simple name for long, multipart names/locations for database objects such as table, function, view, etc. A synonym acts as an abstraction layer for database objects. It provides additional security by protecting a client application from changes made to the location or name of the base database object. For example, a location such as “db_name.schema_name.table_name” can be simply given a synonym as “table.” It will function in the same manner as the original location/name.
Before we dive deep into this topic, let’s start with the basic syntax and arguments used for creating and dropping a synonym on a database object in the SQL Server database engine.
Syntax and parameters
The basic syntax used for creating a synonym on a database object is as follows :
CREATE SYNONYM synonym_name FOR database_object;The basic syntax used for deleting a synonym on a database object is as follows :
DROP SYNONYM [ IF EXISTS ] synonym_name;The parameters/arguments used in the above-mentioned syntaxes are as follows :
synonym_name: Name of the synonym. Provide a desired name for the database object name or location. It is usually done to simplify or provide a smaller name for an object name/location.
database_object: Name or location of the database object for which you wish to create a synonym. In SQL Server, we can create synonyms for the following types of database objects :
- User-defined tables
- User-defined views
- Stored procedures
- Replication filter procedures
- Various types of SQL functions: Inline, table-valued, and scalar
- Assembly(CLR) objects such as stored procedures, inline functions, scalar functions
Note: We cannot use synonyms for base database objects for some other synonyms. One should also note that a synonym cannot be used as a reference to a user-defined aggregate function.
Having discussed the basic syntax and parameters used for working with synonyms in SQL Server, let’s try a few examples to understand the concept in more detail.
Examples of SQL synonyms
Given below are the examples of SQL synonyms:
Example #1
SQL Query to illustrate the creation of a synonym on a database table object.
Consider a dummy table called “students” for illustration purposes. The table is present in the database “practice_db” and is stored in a schema named “dbo.” The table has the following data in it.
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [roll_no]
,[student_name]
,[degree_major]
,[degree_year]
,[society]
FROM [practice_db].[dbo].[students]
Now we are all set to create synonyms for this table.
1. CREATING A SYNONYM
CREATE SYNONYM syn_student
FOR practice_db.dbo.students
GO
The synonym creation query got executed successfully. We can check if the newly created synonym has been successfully created by going to the object explorer section and looking for the specified synonym under the synonyms header of the concerned database, as shown below.
We can clearly see from the above image that the “syn_student” synonym has been successfully created on the database object “students” table.
In this section, we have learned the creation of a synonym, and in the next section, we will see that synonyms work as well as original names and locations when performing SELECT, UPDATE, INSERT, EXECUTE, DELETE, SUB-SELECT statements.
2. USING AND WORKING WITH SYNONYMS
When working with synonyms in SQL Server, the base database object is affected in the same manner as it gets affected when the original name or location of the object is used. For example, if you try to insert a new row or update a column value in the synonym, the said changes will be made in the base object itself.
SELECT * FROM syn_student;
Here is a simple SELECT * statement to select all the records from the “students” table using its synonym. By now, you might have observed that the SELECT statement returns the same output as the SELECT statement of the first query of this article.
Now you must be wondering if it is even possible to use the columns of the original database objects using its synonym. The answer to your question is a big “Yes!”. Yes, we can fetch specific columns and records from the said table using its synonym, as shown below.
SELECT student_name, degree_year,society
FROM syn_student
WHERE degree_major = 'Computer Science Engineering';
In this example, we have fetched details such as student_name, degree_year, and society for only students majoring in “computer science engineering,” using the synonym of the student’s table. And as can be seen from the output of the query, the results seem to be correct.
Example #2
SQL Query to remove a synonym on a database object.
DROP SYNONYM syn_student;
The command got executed successfully, which means the “syn_student” synonym on the “students” table has been successfully deleted.
Conclusion
In this article, we have learned about synonyms and their uses. Synonyms are used as simple identifiers for database objects such as tables, views, stored procedures, and functions. They provide a layer of abstraction for client applications.
Recommended Articles
We hope that this EDUCBA information on “SQL synonyms” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
