Trending October 2023 # Learn The Examples Of Sql Synonyms # Suggested November 2023 # Top 11 Popular

You are reading the article Learn The Examples Of Sql Synonyms updated in October 2023 on the website Benhvienthammyvienaau.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 Learn The Examples Of 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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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]

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.