Synonyms in SQL Server

Introduction

Have you ever found yourself building a very complex stored procedure or script that deals with multiple servers and databases? Were you thinking to yourself "I pity the fool that has to re-aim this thing when the server name changes!"? Yea, we have all been there! Did you ever have a need to be able to easily redirect where your script was pulling or putting the data? SQL Server 2005 and up has the answer for you and they are called Synonyms! Let's take a look!   

Synonyms

What is a synonym ? A synonym is nothing more than a pointer to an object that SQL server maintains in a database. These pointers are resolved at run time so that means you can create one without the object it points to actually existing. This can be a good thing or a bad thing depending on how you look at it. To create a synonym , you must have the CREATE SYNONYM permission or be in a group with this privilege. 

Make sure you are in the database you wish to have the synonym in. The command to create a synonym is:

USE [DATABASENAME}
GO
CREATE SYNONYM [dbo].[SYNONYM_NAME] FOR [OBJECT]
GO

Now let's see how we can use this to our advantage. If I have a query that references [SERVERA].[OLTP_DB].[dbo].[MyDateTable], If I create the synonym below:

USE [TestDB]
GO
CREATE SYNONYM [dbo].[Remote_OLTP] 
   FOR [SERVERA].[OLTP_DB].[dbo].[MyDateTable]
GO

I can now use the synonym in place of the table name in my query.

SELECT TOP 10 * FROM [dbo].[Remote_OLTP].

Not only does this shorten my script, but it makes it very easy to re-point my script to [SERVERB] without having to modify the code just by changing the synonym. There is no ALTER statement for this task current to this writing so you simply drop and recreate it.

USE [TestDB]
GO
DROP SYNONYM [dbo].[Remote_OLTP]
GO
CREATE SYNONYM [dbo].[Remote_OLTP] 
   FOR [SERVERB].[OLTP_DB].[dbo].[MyDateTable]
GO

Now the same query is pulling data from ServerB instead of ServerA and I don't have to write that long line of reference to it anymore. Win/Win!

The Good and the Bad

You can point a synonym at almost anything in SQL server. Some but not all of the examples are:

• Tables
• Views
• Stored Procedures
• Temporary tables both local and global.

The key thing to remember is it is database specific. 

Some of the positive points of synonyms are:

1. They provide a layer of abstraction between the server and your code.
2. They allow you to replace complex multi part object names with simple easy to type names.
3. They provide the flexibility to easily change the objects your scripts reference with a change to a single object. Did I mention abstraction enough? You get the point.

Just some of the bad things you'll need to watch out for while using synonyms that I have come across, and this is by no means a complete listing:

1. You can read the code, but you can't be sure where it's pointing until you check the synonyms.
2. Synonyms are loosely bound. This means they could get dropped or changed and your script won't know until it fails while running.
3. You can't make a synonym for a synonym. This is sometimes termed chaining from an OOP standpoint. 
4. You cannot have an object name with the same name as a synonym. I usually use a prefix on mine to make them unique, but I do that with most objects in my databases. 
5. You can't use synonyms in a DDL statement. Pretty clear statement of the obvious there. 

Conclusion

I hope you find the use of synonyms in your future to be rewarding. I would like to offer a few suggestions in closing. Prefix your synonyms so that they don't consume useful object names in your database. Comment your scripts and stored procedures that use synonyms at the top so that others know to look. I usually put the create commands in the bottom of a script commented out so that they can easily be recreated as designed if someone changed them and forgot to script them out first.