SQL synonyms

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 :

1. User-defined tables
2. User-defined views
3. Stored procedures
4. Replication filter procedures
5. Various types of SQL functions: Inline, table-valued, and scalar
6. 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

This is a guide to SQL synonyms. Here we discuss the basic syntax and parameters used for working with synonyms in SQL Server. You may also have a look at the following articles to learn more –

1. SQL Alias
2. SQL HOUR()
3. MySQL Repair Table
4. PostgreSQL TEXT