Create a SQL Alias using SQL Server Synonyms to access to Tables, Views, User-Defined Functions, Stored Procedures

By:   |   Updated: 2022-09-21   |   Comments (2)   |   Related: > Synonyms


I'm new to the DBA world and just heard about something called a "synonym." What is a synonym? Why and how do I use a synonym? What benefits are there to using a synonym?


In this tutorial, we answer all those questions and possibly a few more that you may not have thought to ask. You will learn how to create and delete synonyms, and we will explain why you need to use them.

What is a SQL Server Synonym

In Microsoft SQL Server, a synonym is an alias or alternative name for a database object such as a table, view, user-defined function, stored procedure, etc. You can create or drop a synonym, but there is no option to modify an existing synonym.

Microsoft SQL Synonym Points of Interest

  • A synonym must be given a unique name, much like other database objects.
  • A synonym cannot be a base object for another synonym.
  • A synonym cannot reference a user-defined aggregate function.
  • A synonym cannot be modified or rename the object. It must be dropped and re-created.

Basic Syntax for Create Synonym Statement

FOR myTable;

Basic Syntax for Dropping a Synonym


What is the Purpose of a Synonym

There are several reasons you should create synonyms for your database objects. One reason you should use synonyms is to mask your object's actual location and name, whether that object is a table, view, stored procedure, etc. This masking with a synonym offers a significant level of security. After all, the primary objective of a DBA is to "protect the data."

Another example would be to reference a table frequently with a long, descriptive name, yet it takes extra time to type it whenever you run a simple query. You can create a short, but descriptive synonym to reference the table as if you were calling the full table name.

Let's see it in action. Suppose a table that has a long table name. In our sample, let's call this table "HumanResourcesEmployee." Now, whenever a user needs to query data from that table, they must type in the full name of "HumanResourcesEmployee."

If you create a synonym for that table, the user only needs to call the table by its synonym. Let's give this table "HumanResourcesEmployee" a new reference name by creating a synonym called "Employee."

Here's an example for creating the "Employee" synonym for the "HumanResourcesEmployee" table.  We can run this code in SQL Server Management Studio (SSMS):

FOR HumanResourcesEmployee;

From this point on, we can query data from that table with a SELECT statement using the synonym instead of the full table name.


FROM Employee

Now that we have a synonym for this table, Human Resources Department (HR) called and needs to change the name from "Employee" to "Employees." If you change the actual table name from "HumanResourcesEmployee" to "HumanResourcesEmployees," we will probably have some problems. Changing the name of an active table could break scripts and stored procedures since they are referencing the table by its original full name.

However, if you created a synonym for that table, as we did in our sample, you can drop the current "Employee" synonym and create a new one called "Employees" to satisfy the client.



FOR HumanResourcesEmployee;

Now the client is happy with the name change, and no issues popped up because the actual table name did not change, only the synonym we assigned to it.

Modifying a Table Via its Synonym

In this section, we will create the table previously discussed and name it "HumanResourcesEmployee." Next, we'll assign it a synonym and insert data into the table using the synonym, not the full table name.

CREATE TABLE HumanResourcesEmployee(
   , FirstName VARCHAR(20)
   , LastName VARCHAR(20)
   , HireDate DATE

Before adding data, we should assign the synonym "Employee" to the table "HumanResourcesEmployee."

FOR HumanResourcesEmployee;

Now that we have built our table and assigned a simple synonym to that table, let's populate the table with some generic data. In this step, we will use the SQL INSERT command to populate the table using the synonym, not the full table name. Next, we will query the table with a SELECT statement and again use the synonym, not the full table name.

INSERT INTO Employee(FirstName, LastName, HireDate)
VALUES('John', 'Smith', '2022-08-01')
    , ('Doug', 'Simms', '2018-04-11')
    , ('Henry', 'Adams', '2002-06-15');

Now issue this SQL query to see the results.

FROM Employee;


INSERT and SELECT table using synonym results

As mentioned earlier, we can now modify the table data using the assigned synonym. Changing the synonym will not affect any data in the table nor our ability to modify the data in the table. Let's start with changing the synonym for our "HumanResourcesEmployee" table from "Employee" to "Employees."

First, we need to drop the current synonym and create a new one.


FOR HumanResourcesEmployee;

Now, let's add a new row of data to our table using its new synonym and query the results.

INSERT INTO Employees(FirstName, LastName, HireDate)
VALUES('Casey', 'Alexander', '2008-11-21')

Now query the table to see the results.

FROM Employees;


Change synonym, not original table name

Checking for Synonyms

It is essential to ensure that synonyms are unique. There are a couple of options to list existing synonyms to guarantee uniqueness.

Option 1:

You can view the current synonyms via the SSMS interface in the Object Explorer by following these steps:

  1. Click the + (plus) sign to expand databases.
  2. Click the + sign to the database you want to check.
  3. Click the + sign next to the "Synonyms" folder.

Visual example:

Checking for synonyms option 1

Option 2:

Using T-SQL, you can run the following script to return the synonym name, object name, and type.

SELECT name AS 'Synonym Name', base_object_name, type
FROM sys.synonyms;


Checking for synonyms option 2

Wrap Up

In this tutorial, we learned what a synonym is, how to create a synonym and how to drop a synonym. We also discussed the benefits of using synonyms and some of the pitfalls to avoid when using a synonym.

We also learned that synonyms provide a layer of abstraction and protect the client application in case of a name change or location change to the base object. We learned that we could use a synonym as if we were using the actual object name and how to check for existing synonyms.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aubrey Love Aubrey Love is a self-taught DBA with more than six years of experience designing, creating, and monitoring SQL Server databases as a DBA/Business Intelligence Specialist. Certificates include MCSA, A+, Linux+, and Google Map Tools with 40+ years in the computer industry. Aubrey first started working on PC’s when they were introduced to the public in the late 70's.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-09-21

Comments For This Article

Wednesday, September 21, 2022 - 7:39:31 PM - Aubrey W Love Back To Top (90509)
David Morris,

Thanks for the comment. The updates are being applied.

As for your question,
“if a database is only ever used by an application, would this be of any benefit?”

The answer would be yes, in some situations. Creating a synonym or alias for the table, view, etc. provides a level of security by not listing the real name of the table.
For example, in your “Web.config” or “App.config” file or SelectCommand statement in a C# application.
However, if you use the alias in your application, you will need to update those aliases when it changes on the database.
This is usually a pretty simple process if you use the “update all” option.

Wednesday, September 21, 2022 - 10:39:22 AM - David Morris Back To Top (90506)
There is an error with your examples. You created the table HumanResourcesEmployee, but when you dropped the "Employee" synonym and created a new one "Employees" you created that new one on HumanResourcesStaff and not HumanResourcesEmployee.

My question is if a database is only ever used by an application, would this be of any benefit? It makes sense if people are able to query the database through other means, but not so much for the case where it is only used by an application.

get free sql tips
agree to terms