SQL Server Synonym

An alias or alternative names can be given to any of the database objects like a table, view, stored procedure, user-defined function, and sequence with the help of SQL Server Synonym.

Whenever we create a SQL Server Synonym in a database, the synonym is referenced to a particular database object and that database object is called base object. The location of the base object to which the synonym is referenced can be either in the same database or in some other database in the same server or even on some other instance running on another server.

We can use SQL Server Synonym in various scenarios and take advantage out of them. Some of the scenarios are:

As there are 100's or sometimes 1000's of references in code to a particular object, so in that case, we can assign a synonym to that object.
An additional layer of abstraction can be added to the actual base object to which the synonym is assigned.
The database queries can easily refer to the objects that appear to reside in the current database even though in reality they are present in some other database.
Backward compatibility can also be provided to the old legacy base object that is needed in the newer version of the database.
An additional security layer for the protection of the base object can be provided with the help of SQL Server Synonym.
There are scenarios when we want to move to another database, with the help of SQL Server Synonym we can refer to the base object without thinking about the migration.
The lengthy and confusing object names of a database can be simplified with the help of SQL Server Synonym.
Various issues with the cross-database and server dependencies in downstream environments such as development, test, and quality assurance as part of a continuous integration build process can be easily eliminated with the use of the SQL Server Synonym.

The syntax for creating a Synonym in SQL Server Database is:

CREATE SYNONYM [ name_of_schema. ] name_of_synonym 
FOR name_of_base_object;

In the above-written syntax:

Name_of_schema: The name_of_schema is the name of the schema where the synon
ym is going to be created.
Name_of_synonym: The name_of_synonym represents the name of the synonym.
Name_of_base_object: The name_of_base_object represents the name of the base object to which the synonym is going to be assigned.

How to use Synonym in SQL Server Database?

Let us understand SQL Server Synonym with the help of an example. Let us create a database and select that particular database. The syntax for creating and selecting that database is:

CREATE DATABASE salesdb;

USE salesdb;

As we can see in the image a database by the name of 'salesdb' has been created and selected successfully.

Now, we will create a schema and a table inside that schema. The syntax for the creation of a schema and a table is:

CREATE SCHEMA grocery;

CREATE TABLE grocery.fruits(
      fruit_name VARCHAR(100) NOT NULL,
      quantity  INT PRIMARY KEY IDENTITY 
   );

As we can see in the image a schema named 'grocery' has been created successfully and a table named fruits having two columns to store the quantity and name of the fruits inside the grocery schema is also created successfully.

Now let us add some data to the fruits table. The syntax for the same is:

INSERT INTO grocery.fruits(fruit_name,quantity) VALUES('Apple',12);
INSERT INTO grocery.fruits(fruit_name,quantity) VALUES('Coconut',2);
INSERT INTO grocery.fruits(fruit_name,quantity) VALUES('Blueberry',30);
INSERT INTO grocery.fruits(fruit_name,quantity) VALUES('Apricot',15);
INSERT INTO grocery.fruits(fruit_name,quantity) VALUES('Black Currant',20);
INSERT INTO grocery.fruits(fruit_name,quantity) VALUES('Cantaloupe',6);
INSERT INTO grocery.fruits(fruit_name,quantity) VALUES('Dates',40);

As shown in the image the seven rows of data have been inserted into the fruits table. And the same can be seen as the output of the SELECT command.

Whenever we want to refer to the fruits table, first we need to write the database name followed by the schema name and then the name of the table. To reduce this overhead, we can create a synonym with the syntax: