Introduction to Synonyms in SQL Server

A synonyms in SQL Server are database objects that give an alternative name to the database objects existing locally or on the remote server. Also, they provide an abstraction layer to protect the application from any changes in the base object.

In T-SQL scripts, we use 3-part names for referencing database objects. The format is:

[Database].[Schema].[Object]

For example, suppose we have the following database infrastructure:

• Database Name:         [Azuredemodatabase]
• Schema:                      [SalesLT]
• Object(table name):    Product

The SELECT statement with the 3-part name is as below:

SELECT  [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,[Size]
      ,[Weight]
      ,[ProductCategoryID]
      ,[ProductModelID]
      ,[SellStartDate]
      ,[SellEndDate]
      ,[DiscontinuedDate]
      ,[ThumbNailPhoto]
      ,[ThumbnailPhotoFileName]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [Azuredemodatabase].[SalesLT].[Product]

Similarly, a remote server uses the 4-part naming convention. The additional part is [remote server name]. Hence, the format will be

[Server Name].[Database].[Schema].[Object].

Let’s imagine that you need to rename the [SalesLT].[Product] table to [ProductData].

This table is referenced in multiple stored procedures, functions, and views. Therefore, you need to modify all such references in queries or client applications to use the latest name of the table.

You can create a Synonym referencing the database object and use it in your SQL queries. Should any changes happen, you only need to recreate the Synonym definition. You won’t need to change any object references in queries because we are using Synonym.

In a different case, we create a synonym that points to table [Azuredemodatabase].[SalesLT].[Product]. Then, we drop the table and create a view with a similar name. In this case, a Synonym will automatically reference the view because object binding happens with the object name.

If you move an object to a different database, the Synonym helps you to minimize efforts when making changes. Once you recreate the Synonym, all SQL queries automatically get the latest object location.

You can also use Synonyms to conceal the database objects’ names. Users can query Synonyms for retrieving results instead of querying the base table.

You could define a Synonym in SQL Server for the following objects:

• User-defined table
• Stored procedure
• View
• Scalar and inline table-valued functions
• Local and global temporary tables
• CLR stored procedure , functions(table-valued, aggregate, scalar)

Note: The synonym name should be unique in a database.

Where to Use Synonyms in SQL Server

We can use Synonyms in the T-SQL statement, such as Select, Update, Execute, Insert, Delete, and sub-queries.

However, we can’t use Synonyms in data definition language (DDL) statements like Create and Alter. Also, Synonyms aren’t suitable for Check constraints, computed columns, default expressions, Rule expressions, Schema-bound views, and functions.

Create a Synonym in SQL Server in Local Server

To create Synonyms in SQL Server, we use the CREATE SYNONYM statement. The syntax is the following:

CREATE SYNONYM <synonym_name, sysname, sample_synonym>
  FOR <schema_name, sysname, Production>.<object_name, sysname, Product>
GO

For example, let’s create the synonym [MyProductCatalog] for [Azuredemodatabase].[SalesLT].[Product].

CREATE SYNONYM MyProductCatalog FOR [Azuredemodatabase].[SalesLT].[Product]

Once it is created, you can replace the table name with the Synonym as shown below. It internally references the base table that we referenced in the CREATE SYNONYM statement.

SELECT  [ProductID]
      ,[Name]
      ,[ProductNumber]
      ,[Color]
      ,[StandardCost]
      ,[ListPrice]
      ,[Size]
      ,[Weight]
      ,[ProductCategoryID]
      ,[ProductModelID]
      ,[SellStartDate]
      ,[SellEndDate]
      ,[DiscontinuedDate]
      ,[ThumbNailPhoto]
      ,[ThumbnailPhotoFileName]
      ,[rowguid]
      ,[ModifiedDate]
  FROM MyProductCatalog

Create a Synonym in a Database on the Remote Server

Suppose the object exists in a remote server [ABC]. You can create a Synonym to avoid specifying a 4-part name in queries. In the below query, the base object is in the [MyRemoteServer]:

EXEC sp_addlinkedserver MyRemoteServer;
GO  
USE tempdb;  
GO  
CREATE SYNONYM MyProductCatalog FOR MyRemoteServer.[Azuredemodatabase].[SalesLT].[Product]
GO

Creating a Synonym with SQL Server Management Studio

To create Synonyms, we can use the GUI of SQL Server Management Studio.

• Connect to your SQL instance, expand the database, and navigate to the Synonyms folder.
• Right-click on it and choose New Synonym.
• Enter the required details for the Synonym name, Synonym schema, Database Name, Object schema, Object Type, and name.

In our example, we create a Synonym in the schema [HumanResources], the same as of the table schema:

Click on Script to get an equivalent T-SQL script as below:

USE [AdventureWorks2017]
GO
CREATE SYNONYM [HumanResources].[MyEmpData] FOR 
[AdventureWorks2017].[HumanResources].[Employee]
GO

Synonym for User-defined Functions

First, create the UDF dbo.TestSynonym using the below script:

CREATE FUNCTION dbo.TestSynonyn (@ID int)  
RETURNS int  
AS  
BEGIN  
IF @ID < 0  
BEGIN  
    SET @ID=100
END  
RETURN(@ID);  
END;  
GO

Then, create a synonym for it with the name dbo.UDFTest. You can call the UDF using the Synonym and retrieve the query results:

CREATE SYNONYM dbo.UDFTest FOR dbo.TestSynonyn;  
GO  
Declare @ID INT=-10
Select @ID as OrigninalValue, dbo.UDFTest(@ID) as modifiedValue

Update Statement and Synonym

Suppose you want to update a value in the SQL table. If you have defined a Synonym on it, you can utilize it as well. For example, the below update statement uses Synonym [Perofmancetest] in place of the SQL table name:

Update performancetest set [Name]='Updated New value' where ID=1

Dropping a Synonym

You can use the DROP SYNONYM statement to drop a specific synonym in the database. The below query drops [EmpData] in the AdventureWorks2017 database:

Use AdventureWorks2017
Drop Synonym EmpData

Getting the List of Database Synonyms in SQL Server

Assume you want to know existing synonyms in a database along with their base object. You can query the sys.synonyms system catalog view in the respective database:

SELECT 
    name, 
    base_object_name, 
    type
FROM 
    sys.synonyms

In the query output, you get the following information:

• Synonym name
• The base object (3-part or 4-part object name)
• Object type ( SN = Synonym)

What if we Modify the Synonym’s Base Object?

Let’s consider the impact of modifying the base object of a synonym. We have a query to perform several tasks:

• Create a synonym (the Synonym name is [dbo].[EmpData] for the Base Object [AdventureWorks2017].[DBO].[Emp])
• Drop the base object (table) [AdventureWorks2017].[DBO].[Emp]
• Create a view with the name [DBO].[Emp] in the [AdventureWorks2017] database.

USE [AdventureWorks2017]
GO
CREATE SYNONYM [dbo].[EmpData] FOR [AdventureWorks2017].[DBO].[Emp]
GO
Drop table [AdventureWorks2017].[DBO].[Emp]
Go
Create view [DBO].[Emp]
as 
Select * from dbo.Employee
go

As mentioned earlier, the Synonym binds an object using its name. Thus, it should point out to the view instead of the table. In this case, the [dbo].[Emp] table does not exist.

As shown below, it is a view in the [AdventureWorks2017] database.

Alternatively, you can use OBJECTPROPERTYEX() to check the synonym’s object base type:

SELECT OBJECTPROPERTYEX(OBJECT_ID('Emp'), 'BaseType') AS BaseType;  
GO

Here, the Base type references to the database view. In the case of the SQL table, you get U in the output.

Do Synonyms Impact Query performance Negatively?

Many database professionals prefer not using use synonyms in SQL Server. Their point is that SQL Server has to perform an additional step in resolving the base table from the Synonym. Thus, it might produce a negative effect.

Let’s test the query performance. We retrieve records from the table name and the Synonym:

Create table TestTable
(
    ID int,
    [Name] varchar(20)
)
Insert into TestTable values (1, 'Temporary Data')
GO 10000

Create synonym performancetest for TestTable
SET STATISTICS IO ON;
Select * from TestTable 
Go
Select * from performancetest

First, let’s check the actual execution plan. Both query batches have the same execution plan and operator costs:

Similarly, both queries have 41 logical reads and one scan count for retrieving desired output.

SQL Server resolves the synonym base object names in the binding phase of query execution. This happens before the query optimization phase. Therefore, you see a similar execution plan and no performance impact. Consequently, you can use synonyms to avoid long 3-part or 4-part names for frequently accessed objects. It does not impact query performance.

It does not mean you create a Synonym for each object such as a table, stored procedure, functions, views. You can use them for most frequently accessed objects for quick referencing them in queries.

Conclusion

Synonyms in SQL Server can be beneficial. They simplify the database object names by avoiding 3-part or 4-part long names. You can use them for referencing both local and remote objects. It does not produce any performance issues. Therefore, you can explore using them for flexibility in writing queries.  However, best of all is to define synonyms only for the frequently used objects in SQL scripts.

Tags: database, sql server, synonyms Last modified: September 16, 2021