SQL Server Synonym

Summary: in this tutorial, you will learn about SQL Server synonym and how to create synonyms for database objects.

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.

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;
Code language: SQL (Structured Query Language) (sql)

The object is in the following form:

[ server_name.[ database_name ] . [ schema_name_2 ]. object_name   
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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
Code language: SQL (Structured Query Language) (sql)

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

CREATE SCHEMA purchasing;
GO
Code language: SQL (Structured Query Language) (sql)

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
);
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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

SELECT * FROM suppliers;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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  
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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.

Benefits of synonyms

Synonym provides the following benefit if you use them properly:

• Provide a layer of abstraction over the base objects.
• Shorten the lengthy name e.g., a very_long_database_name.with_schema.and_object_name with a simplified alias.
• Allow backward compatibility for the existing applications when you rename database objects such as tables, views, stored procedures, user-defined functions, and sequences.

In this tutorial, you have learned how to about the SQL Server synonyms and how to use them effectively in your applications.