In SQL Server, a synonym is a database object that provides an alternative name for another database object, such as a table, view, stored procedure, or function. Synonyms act as aliases, allowing us to reference objects using different names without changing the underlying object itself.

The underlying object that the synonym references is typically referred to the base object. Either way, the synonym allows us to reference the base object without actually referring to its name (we refer to the synonym’s name instead).

Benefits of Synonyms

Synonyms offer several benefits:

• Simplifying object names: We can create shorter, more memorable names for complex object names.
• Abstracting object locations: Synonyms can reference objects in different databases or even on different servers.
• Enhancing code portability: By using synonyms, we can easily switch between development and production environments without modifying our code.
• Improving security: Synonyms can provide limited access to objects without exposing their actual names or locations.

Examples

Let’s look at some examples to better understand how synonyms work in SQL Server.

Example 1: Creating a Synonym for a Local Table

Suppose we have a table called EmployeeDetails, but we want to be able to reference it simply as Employees. In this case, we could create a synonym called Employees that references the table in question.

Here’s the code for creating the original table and the synonym that references it:

-- Create the table
CREATE TABLE dbo.EmployeeDetails
(
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

-- Create the synonym
CREATE SYNONYM Employees FOR dbo.EmployeeDetails;

Now we can query the table via the synonym:

SELECT * FROM Employees;

This query is the equivalent of the following:

SELECT * FROM EmployeeDetails;

Example 2: Creating a Synonym for a Table in Another Database

Here’s an example of creating a synonym for a table in another database:

CREATE SYNONYM CurrentEmployees FOR HR.dbo.ActiveEmployees;

Again, we can query the table via its synonym:

SELECT * FROM CurrentEmployees 
WHERE Department = 'Sales';

Example 3: Creating a Synonym for a Stored Procedure

Synonyms aren’t limited to tables. We can also use them for other objects, such as stored procedures:

-- Create a synonym for a stored procedure
CREATE SYNONYM GetEmployeeInfo FOR dbo.sp_GetEmployeeDetails;

-- Execute the stored procedure using the synonym
EXEC GetEmployeeInfo @EmployeeID = 1001;

Which Objects Can We Create a Synonym For?

We can create synonyms for the following object types:

• Assembly (CLR) aggregate functions
• Assembly (CLR) scalar function
• Assembly (CLR) stored procedure
• Assembly (CLR) table-valued function
• Replication-filter-procedure
• SQL inline-tabled-valued function
• SQL scalar function
• SQL stored procedure
• SQL table-valued function
• Table (local and global temporary user-defined tables)
• View

We can’t create a synonym based on another synonym. Also, a synonym can’t user-defined aggregate function.

Where Can We Use Synonyms?

We can use synonyms in the following contexts:

• SELECT statements
• UPDATE statements
• EXECUTE statements
• INSERT statements
• DELETE statements
• Subselect expressions

Here, when we use the synonym, the base object is affected as if we’d applied the statement to the base object. For example, if we insert data into a synonym, it will insert it into the base object.

If we pass the synonym’s name to the OBJECT_ID() function, we’ll get the ID of the synonym (not the base object).

We can’t reference synonyms in DDL statements. For example, we can’t pass the synonym’s name to the ALTER TABLE statement and expect it to work. We would need to pass the table’s name itself.

Synonyms aren’t schema-bound and therefore can’t be referenced in the following schema-bound contexts:

• CHECK constraints
• Default expressions
• Schema-bound views
• Computed columns
• Rule expressions
• Schema-bound functions

Getting a List of Synonyms

We can use the sys.synonyms view to return a list of synonyms in a given database:

SELECT * FROM sys.synonyms;

That includes a bunch of info, including the base object’s name (with its path).