Synonyms in SQL Server

Are you familiar with synonyms as they relate to SQL Server? I haven’t seen them used too much out in the wild but understand they can have benefits. Let’s take a look at what synonyms are in SQL Server and some common reasons for implementing them.

So Close

A synonym in SQL Server allows you to give a different name to a database object. While you may just want an easier name for an object, a synonym can also protect from issues if the source of the synonym is changed.

Microsoft gives the example of a synonym for an object that moves from one server to another. If a synonym is configured for an object on Server1 and the object moves to Server2, the synonym can be dropped, recreated, and the rest of your code can carry on successfully while using the synonym. Without the synonym, you would potentially be looking at a lot of “Find/Replace” to update the server names in code.

Synonyms in Action

We’ll create a couple synonyms in the StackOverflow2013 database to use objects in the ExampleDB database on the same server.

We can create a synonym for the Comic table:

USE StackOverflow2013;
GO

CREATE SYNONYM ComicTable
FOR ExampleDB.dbo.Comic;
GO

And the access the data in StackOverflow2013 by running:

SELECT Title FROM ComicTable;
GO

We can do the same for a stored procedure:

CREATE SYNONYM ProcFromExampleDB 
FOR ExampleDB.dbo.ExampleProc;
GO

And then run ProcFromExampleDB:

EXEC ProcFromExampleDB;
GO

We’ll need to include four parts to reference a server if our synonym involves an object on a linked server. If we have a Comic table on a linked server called Server2, we would create a synonym by running:

CREATE SYNONYM Server2Comic
FOR [Server2].[ExampleDB].[dbo].[Comic];
GO

Search and Drop

We can check a database for existing synonyms by running this query:

SELECT name, base_object_name, type
FROM sys.synonyms
ORDER BY name;
GO

Or by expanding the Synonyms folder under the database in SSMS:

No longer need a synonym? Drop it with this query:

DROP SYNONYM IF EXISTS ComicTable;

Thanks for reading!