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: More > Synonyms


Problem

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?

Solution

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

 CREATE SYNONYM mySynonym
 FOR myTable;
 

Basic Syntax for Dropping a Synonym

 DROP SYNONYM mySynonym;
 

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):

 CREATE SYNONYM Employee
 FOR HumanResourcesEmployee;
 GO
 

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

Example:

 SELECT *
 FROM Employee
 GO
 

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.

Example:

 DROP SYNONYM Employee;
 
 CREATE SYNONYM Employees
 FOR HumanResourcesEmployee;
 GO
 

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(
 id INT IDENTITY
 , FirstName VARCHAR(20)
 , LastName VARCHAR(20)
 , HireDate DATE
 );
 GO
 

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

 CREATE SYNONYM Employee
 FOR HumanResourcesEmployee;
 GO
 

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');
 GO
 

Now issue this SQL query to see the results.

 SELECT *
 FROM Employee;
 GO
 

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.

 DROP SYNONYM Employee;
 
 CREATE SYNONYM Employees
 FOR HumanResourcesEmployee;
 GO
 

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.

 SELECT *
 FROM Employees;
 GO
 

Results:

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:

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;
 GO
 

Results:

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



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights








About the author
Aubrey Love has been a Database Administrator for about 8 years and is currently working as a Microsoft SQL Server Business Intelligence specialist.

View all my tips


Article Last Updated: 2022-09-21

Comments For This Article




Wednesday, September 21, 2022 - 7:39:31 PM - Aubrey W LoveBack 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 MorrisBack 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.