Synonym in SQL Server

Synonym

A synonym is an alias or an alternative name given to the other objects in the database. The alternative name can be used to refer to the underlying objects in different contexts such as SELECT, INSERT, DELETE, UPDATE and EXECUTE. SQL Server supports synonym for user table, view, stored procedure, table-valued function, etc., while synonym is not schema bounded, any changes to the base object is not known until run time.

Why Synonym?
Synonym provides an abstraction to the base object. So wherever synonym is used as an alternative to the base object name, changes to underlying objects don’t have any impact on the client applications.

Consider a scenario,
A web service is connecting to an SQL database to execute an ad-hoc query, that has the base table name as part of the query. let’s say the database engineering team decided to shift several tables from one schema to another within the same database. The schema change must be accommodated within the client applications otherwise the client application will break. When multiple consumers or systems are referring to the same table, the impact is even becoming wider.

When the client application refers to the synonym in the ad-hoc query, change to the base object is seamless. The change to table schema is only needed to be refreshed in the synonym by recreating it.

Synonym Example:
The following script creates a table and populates some records.

DROP TABLE IF EXISTS dbo.OrderTransaction
go
CREATE TABLE dbo.OrderTransaction
(
OrderId INT NOT NULL IDENTITY(1,1),
ProductId INT,
OrderQty INT,
OrderDt DATETIME DEFAULT GETDATE()
)
GO
INSERT INTO dbo.OrderTransaction (OrderQty, OrderDt) DEFAULT VALUES
GO 10
GO
SELECT * FROM dbo.OrderTransaction

Creating Synonym
The following code will create a Synonym for the OrderTransaction table and the SELECT query return records from the base table using the Synonym.

CREATE SYNONYM dbo.OrderTrans FOR Credit.dbo.OrderTransaction

SELECT * FROM dbo.OrderTrans

All the synonym in the database can be queried from the system view sys.synonyms. This view provides synonym name and the schema it is part of and the base object name for which the synonym is created, etc.,

SELECT name, schema_id, type_desc, base_object_name FROM sys.synonyms

Altering Synonym
SQL Server doesn’t provide options to alter synonym as it does for other database objects. When any change needs to be implemented on the Synonym it should be dropped and recreated with the desired changes.

DROP SYNONYM IF EXISTS dbo.OrderTrans

CREATE SYNONYM dbo.OrderTrans FOR Credit.dbo.OrderTransaction

Consider another real-world scenario to understand the power of Synonym,
An organization has a large data warehouse database, that has hundreds of tables for different business units, data for some tables are getting replicated in real time from other database systems, data for some other tables are loaded nightly through ETL solution, etc.,

Many client applications are referring the tables in different ways such as referring tables directly in ad-hoc query, invoking a stored procedure which refers the tables etc.,

The organization decided to shift the tables which are replicated from other system to a separate database on the same server.

The solution for this requirement become very simple with the use of Synonym.

Here are the brief steps involved to achieve shifting the tables to separate database

A new database is created to move all the tables which are part of the requirement.
The replication to be established to the new database from the source system.
Create synonym on the existing database with the same name as the tables. The base object of the synonym would be referring to the table from the new database.

The client application still uses the same name in the ad-hoc query, stored procedures referring the table which moved to another database is also not altered. As the synonym carries the same name as table but the base object of the synonym is pointing to actual table from another database.