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:
- Click the + (plus) sign to expand databases.
- Click the + sign to the database you want to check.
- 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
- Check your current databases for any existing synonyms.
- Start using synonyms. You will be glad you did.
- Finding SQL Server Object Dependencies for Synonyms
- Template to Create SQL Server Synonyms with checks
- Use Synonyms to Abstract the Location of SQL Server Database Objects
