What is a synonym in SQL Server

In SQL Server, a synonym is an alias or alternative name for a database object such as a table, view, stored procedure, user-defined function, and sequence. A synonym provides you with many benefits if you use it properly.

A synonym is a database object that serves the following purposes:

• Provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.

• Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.

For example, consider the Employee table of Adventure Works, located on a server named Server1. To reference this table from another server, Server2, a client application would have to use the four-part name Server1.AdventureWorks.Person.Employee. Also, if the location of the table were to change, for example, to another server, the client application would have to be modified to reflect that change.

To address both these issues, you can create a synonym, EmpTable, on Server2 for the Employee table on Server1. Now, the client application only has to use the single-part name, EmpTable, to reference the Employee table. Also, if the location of the Employee table changes, you will have to modify the synonym, EmpTable, to point to the new location of the Employee table. Because there is no ALTER SYNONYM statement, you first have to drop the synonym, EmpTable, and then re-create the synonym with the same name, but point the synonym to the new location of Employee.

SQL Server CREATE SYNONYM statement syntax

To create a synonym, you use the CREATE SYNONYM statement as follows:

CREATE SYNONYM [ schema_name_1. ] synonym_name 
FOR object;

The object is in the following form:

[ server_name.[ database_name ] . [ schema_name_2 ]. object_name   

In this syntax:

• First, specify the target object that you want to assign a synonym in the FOR clause
• Second, provide the name of the synonym after the CREATE SYNONYM keywords

Note that the object for which you create the synonym does not have to exist at the time the synonym is created.

SQL Server CREATE SYNONYM statement examples

Let’s take some examples of using the CREATE SYNONYM statement to get a better understanding.

A) Creating a synonym within the same database example

The following example uses the CREATE SYNONYM statement to create a synonym for the sales.orders table:

CREATE SYNONYM orders FOR sales.orders;

Once the orders synonym is created, you can reference it in anywhere which you use the target object (sales.orders table).

For example, the following query uses the orders synonym instead of sales.orders table:

SELECT * FROM orders;

B) Creating a synonym for a table in another database

First, create a new database named test and set the current database to test:

CREATE DATABASE test;
GO

USE test;
GO

Next, create a new schema named purchasing inside the test database:

CREATE SCHEMA purchasing;
GO

Then, create a new table in the purchasing schema of the test database:

CREATE TABLE purchasing.suppliers
(
    supplier_id   INT
    PRIMARY KEY IDENTITY, 
    supplier_name NVARCHAR(100) NOT NULL
);

After that, from the BikeStores database, create a synonym for the purchasing.suppliers table in the test database:

CREATE SYNONYM suppliers 
FOR test.purchasing.suppliers;

Finally, from the BikeStores database, refer to the test.purchasing.suppliers table using the suppliers synonym:

SELECT * FROM suppliers;

Listing all synonyms of a database

You can view all synonyms of a database by using Transact-SQL and SQL Server Management Studio.

A) Listing synonyms using Transact-SQL command

To list all synonyms of the current database, you query from the sys.synonyms catalog view as shown in the following query:

SELECT 
    name, 
    base_object_name, 
    type
FROM 
    sys.synonyms
ORDER BY 
    name;

Here is the output:

B) Listing synonyms using SQL Server Management Studio

From the SQL Server Management Studio, you can view all synonym of the current database via Synonyms node as shown in the following picture:

Removing a synonym

To remove a synonym, you use the DROP SYNONYM statement with the following syntax:

DROP SYNONYM [ IF EXISTS ] [schema.] synonym_name  

In this syntax:

• First, specify the synonym name that you want to remove after the DROP SYNONYM keywords.
• Second, use the IF EXISTS to conditionally drop the synonym only if it exists. Removing a non-existing synonym without the IF EXISTS option will result in an error.

Removing synonyms example

The following example uses the DROP SYNONYM statement to drop the orders synonym:

DROP SYNONYM IF EXISTS orders;

When to use synonyms

You will find some situations which you can effectively use synonyms.

1) Simplify object names

If you refer to an object from another database (even from a remote server), you can create a synonym in your database and reference to this object as it is in your database.

2) Enable seamless object name changes

When you want to rename a table or any other object such as a view, stored procedure, user-defined function, or a sequence, the existing database objects that reference to this table need to be manually modified to reflect the new name. In addition, all current applications that use this table need to be changed and possibly to be recompiled. To avoid all of these hard work, you can rename the table and create a synonym for it to keep existing applications function properly.