SQL Server Synonyms

An SQL Server Synonym is a special database object used to an alias or alternative name for an existing object within the local server instance or for objects in a remote SQL Server instance. It applies to such objects as Tables, Views, Stored Procedures, Functions, or Sequences.

Synonyms are schema-bound and should meet unique naming requirements within that particular Schema. In simple words, the Synonym name should be unique within the schema under which it is created out.

We can use Synonyms for Abstraction and several other purposes. We’ll discuss it in more detail further in this article.

SQL Server Synonym Syntax

The syntax to create a Synonym is the following:

CREATE SYNONYM <schema_name>.<synonym_name>
FOR <object_name>;

• Schema_name is an optional value referring to the name of the Schema under which we need to create our Synonym.
• Synonynm_name refers to the name of the Synonym we wish to create.
• Object_name refers to the name of the Base Object which should be referenced by the Synonym. The object name should match any one of the following options:
  • 2-part naming for objects within the database like [schema_name].[object_name]
  • 3-part naming for local server instance-based objects like [Database_name].[schema_name].[object_name]
  • 4-part naming for objects present in remote SQL Server instance using Linked Server [remote_server_name].[Database_name].[schema_name].[object_name]

We can use Synonyms across SELECT, INSERT, UPDATE, DELETE, or EXECUTE commands, but can’t use them with DDL statements like CREATE OR ALTER OR DROP statements to modify the underlying base objects.

Let’s get some hands-on experience by creating a couple of Synonyms in the AdventureWorks database. Use the below script:

CREATE SYNONYM PMPDescriptionCulture
FOR [Production].[ProductModelProductDescriptionCulture];

This script creates a synonym to address a table having a very lengthy name (ProductModelProductDescriptionCulture) with a shorter SYNONYM version.

After creation, we can easily type the shorter version of the table name as shown below:

SELECT TOP 10 * 
FROM [Production].[ProductModelProductDescriptionCulture];

-- To Fetch data using Synonym within AdventureWorks database
SELECT TOP 10 * 
FROM PMPDescriptionCulture;

-- To Fetech data using Synonym from other database using 3-part naming using Synonym
SELECT TOP 10 * 
FROM AdventureWorks.dbo.PMPDescriptionCulture;

As discussed earlier, Schema_name before a synonym is optional. We can create the synonym with a schema name as follows as well:

CREATE SYNONYM Production.PMPDescriptionCulture
FOR [Production].[ProductModelProductDescriptionCulture];

To fetch data from this synonym, we can use queries in any of the below formats:

SELECT TOP 10 * 
FROM Production.[ProductModelProductDescriptionCulture];

-- To Fetch data using Synonym within AdventureWorks database
SELECT TOP 10 * 
FROM Production.PMPDescriptionCulture;

-- To Fetech data using Synonym from other database using 3-part naming using Synonym
SELECT TOP 10 * 
FROM AdventureWorks.Production.PMPDescriptionCulture;

Now, we can try creating a SYNONYM in the AdventureWorks database that will reference a non-existent object name from the Test database.

To confirm that the object does not exist, let’s try a SELECT operation on the object:

SELECT * 
FROM Test.dbo.Person;

We have received the error message: Invalid Object name Test.dbo.Person.

Let’s try to create a Synonym in AdventureWorks on this non-existing object to see is we can create the SYNONYM at all:

CREATE SYNONYM Person 
FOR Test.dbo.Person;

Even though Test.dbo.Person doesn’t exist, the SYNONYM is successfully created. It proves that the SYNONYM creation doesn’t validate or create references.

Let’s try a SELECT on this SYNONYM now:

SELECT *
FROM Person;

The script execution produced another error because the referenced object name doesn’t exist:

Let’s create a table in the Test database with several columns:

CREATE TABLE Test.dbo.Person ( a int, b varchar(10));

Let’s try executing SELECT on the SYNONYM Person:

SELECT *
FROM Person;

Now, we can summarize the Synonyms’ behavior:

1. Synonyms don’t validate the referenced object names during the time of creation. Hence, the object name doesn’t have to exist at the time of Synonym creation.
2. Synonyms validate objects during run-time only. In other words, verification of the object name will be performed during the run-time execution and not referenced during other timings. The base object can be dropped, modified, recreated, or replaced by another object with the same name. The synonym can still work.
3. Synonyms are not schema-bound. Hence, we can drop them without any issues. However, it can introduce the risk of leaving orphaned references in the application code.
4. Using Synonyms doesn’t affect performance because SQL Server resolves synonyms with the actual object names that are referenced inside during the query execution or Execution Plan creation.

How to View Synonyms

Once we’ve created Synonyms, we can view them all inside the Database under the Synonyms category:

If you prefer using T-SQL, we can see the list of all Synonyms by applying the sys.synonyms view along with the OBJECTPROPERTYEX function:

SELECT OBJECT_SCHEMA_NAME(s.object_id, DB_ID()) Schema_name
	, s.name Table_name
	, s.base_object_name 
	, OBJECTPROPERTYEX(OBJECT_ID(s.[name]), 'BaseType') as BaseType
	, ao.type_desc
FROM sys.synonyms s
JOIN (SELECT DISTINCT type, type_desc from sys.all_objects) ao on CONVERT(varchar(2),OBJECTPROPERTYEX(OBJECT_ID(s.[name]), 'BaseType')) = ao.type

How to Drop Synonyms

To drop a Synonym, we can simply use the DROP SYNONYM statement:

DROP SYNONYM [IF EXISTS] [schema_name].synonym_name;

IF EXISTS keyword refer to drop synonym if exists only

schema_name is optional. We only need it if we create our SYNONYM with the schema_name value.

To drop the Production.PMPDescriptionCulture Synonym, we use the below script:

DROP SYNONYM IF EXISTS Production.PMPDescriptionCulture;

SYNONYM Permissions

Similar to granting permissions on the actual Database objects, we can grant GRANT, DENY, REVOKE permissions on SYNONYMS.

If the underlying or referenced object is:

• a Table, we can grant SELECT, INSERT, UPDATE, or DELETE permissions on a Synonym.
• a View, we can grant SELECT, INSERT, UPDATE, DELETE, or VIEW DEFINITION permissions on a Synonym.
• a Stored Procedure, we can grant EXECUTE or VIEW DEFINITION permissions on a Synonym.
• a Function, we can grant EXECUTE or VIEW DEFINITION permissions on a Synonym.

Practical Usages of Synonyms

Frequently Changing Object Names. In a typical development environment, developers might have referenced a Linked Server object or an object under some other database. During Production Migration, they have to carefully migrate all these to new object names. If not done, it can cause a lot of issues. To resolve this issue, developers can create a synonym on the Linked Server object or object from another database. Then, they can use these synonyms across multiple views or procedures, or in the application code. When the time comes for the Production Migration, they would simply modify the correct base referenced object for a synonym in only one location.

Easily remember complicated Object Names. Instead of remembering a lengthy object name that can consist of up to 4 parts, we can create a shortened Synonym. It is easier to remember and type faster, without mistakes. You can use the Code Completion SSMS add-in to type code accurately, as it helps you eliminate any spelling mistakes.

Secure Synonyms. Similar to securing Base objects, we can grant Granular permissions on Synonyms and control who may access a particular synonym or object.

Easier Object Name Changes. Assume that we need to rename a table or view which is referenced across many other objects. It will take a lot of time to carefully analyze the database structure before renaming the source base object. Instead, we can rename the source base object and create a synonym with the actual base object name, so that any other references to that particular object can still execute without any issues. Even though it’s not an ideal scenario, it would be helpful in time-critical bug fixes. Then we can fix it perfectly with a complete analysis.

Abstraction of Base objects. To hide the actual name of the base objects from users or vendors, we can simply create a Synonym. Then we’d grant access for the Synonyms and won’t expose the actual Table or View names.

No Performance Impacts. Similar to resolving an alias name with the actual Table or view names, SQL Server can resolve the actual object name referenced by Synonyms while preparing the Execution plan.

Summary

We have familiarized ourselves with Synonyms in SQL Server and learned how to create, view, or drop them. Also, we’ve understood how to use SQL Server Synonyms and when it is beneficial to apply them.

Synonyms are an excellent choice to create an Alias name or an Abstract layer for sensitive objects and then secure the permissions at the Synonym level itself. However, even though Synonyms don’t produce any Performance impacts and can be useful for working on Migration environments, you should be careful when using them. Without complete documentation, some users may perform operations on unwanted synonyms or base objects by mistake.

Stay tuned for the next articles!