Print preview: Practical Uses for Synonyms in SQL Server

Practical Uses for Synonyms in SQL Server

By Jason Shadonix, 2014/08/21

Overview

The concept of a synonym was introduced in SQL Server 2005.  Synonyms are very simple database objects, but have the potential to save a lot of time and work if implemented with a little bit of thought.  This article discusses what exactly a synonym is, and a few examples of how to use them, some of which I have used myself with success.



What is a Synonym?

A synonym, like the name implies, is nothing more than an alternate name you create for another database object.  It sounds really simple, but if you think about it, this can be very useful for DBAs and developers if applied carefully.



Creating Synonyms

The following syntax is used to create a synonym:

CREATE SYNONYM <synonym_name> 
FOR <server_name>.<database_name>.<schema_name>.<object_name> 

You can create synonyms for Stored Procedures, Functions, Tables (even temp tables!), and Views. The object you are creating the synonym for does NOT have to actually exist at the time the synonym is created.  

For example, if you wanted to create a synonym to point to a customers table on another server, you would do so as follows:

CREATE SYNONYM Customer 
 FOR CustomerServer.CustomerDatabase.dbo.CustomerList

You could then reference the synonym instead of the remote object:

SELECT * 
 FROM Customer

Instead of

SELECT * 
 FROM CustomerServer.CustomerDatabase.dbo.CustomerList

The sys.synonyms catalog view can be used to get a list of synonyms you have in a given database.  This will give you the name of the synonym, and the name of the object the synonym is created for.

See the links below in "References/Further Information" section for the Books Online articles for Synonyms.



Practical Uses

Now we know what a synonym is and how to create one.  But how can they be used in practice to make our lives easier?  Here are a few examples of how they can be used, some of which I have used myself.



Simplify Object Names

One way they can be used is simply to shorten or clarify the names of objects with long or hard to decipher names.  If you have a need in a query to reference a table on a remote server that you don't control, you may find the name to be unintuitive.  For example, you may have a need to use this crazy four-part name to get to a table:

ProdSQLServer2345.CustDatabase293.dbo.tblSalesNbrsMar2014  

You could simple create a synonym to point to that object, and then reference that synonym in your queries.

CREATE SYNONYM CurrentSalesData 
 FOR ProdSQLServer2345.CustDatabase293.dbo.tblSalesNbrsMar2014 

Using "CurrentSalesData" instead of "ProdSQLServer2345.CustDatabase293.dbo.tblSalesNbrsMar2014." will make your queries easier to write and understand.

Allow for seamless object name changes

It occasionally becomes necessary to rename SQL objects in your databases.  This can prove to be a difficult tasks if you have a large number of queries or applications that refer to that object.  One way to ease the pain of renaming an object is to create a Synonym pointing to that object, and always use the Synonym in your queries from the beginning.  This will allow you to change object names with a lot less effort.  Instead of hunting around and finding all references to that object and updating it, you simply need to drop and recreate the Synonym and you are good to go.  Your queries and application can continue to simply use the Synonym to access that object, and will have no clue that its actual name has changed.

Using the example from above, when April 2014 comes around, you could drop the CurrentSalesData synonym, and create a new one pointing to tblSalesNbrsApr2014.

I've personally used this, and found that it works well.  I've even been involved with the design of a web application where we created a Synonym for every single table in the database and had the code use Synonyms exclusively.

Allow for seamless object moves

Just as you can use a synonym to allow you to change the name of an object without your applications or users knowing the difference, you can also easily move an object to a different database (or even a different server) if you have your application and queries using the synonym for all queries involving that object.  Simply drop and recreate the synonym with the new object name and you're back in business!



Hide true object names 

If you have a need to allow someone to query your databases, but have a reason to not reveal object names within your environment, you can create a synonym and allow people to use it rather than directly accessing the database objects.  Security through obscurity!



Managing which environment you are using

If you have identical production and development/QA environments, synonyms can be useful to temporarily point queries to a different environment to help with troubleshooting.  For example, if you have a synonym named SalesData in each environment pointing to the appropriate SalesData table, you can temporarily repoint the synonym to the table in a different environment to assist with testing or troubleshooting.  Note that care must be exercised when doing this to avoid inadvertently updating production data while testing, or exposing sensitive production data to people that should not be seeing it.



Potential "Gotchas"

In my experience, I've never had any issues with synonyms causing performance issues or simply "not working".  I have found, however that they can become a little bit burdensome to manage if you go to the extreme of creating a synonym for every table in your database.  I've also confused myself a couple of times troubleshooting issues that weren't really issues simply because I forgot a synonym existed.

In some cases involving tables, you might also find that using Views instead of Synonyms to be an equally good if not better solution.



Conclusion

These are just a few uses of synonyms I've heard of or used in my experience.  With a little thought, they can be very useful.  I'd be interested in hearing other creative ways people have used synonyms in practice.



References/Further Information

General Synonym info: http://msdn.microsoft.com/en-us/library/ms187552(v=sql.110).aspx

CREATE SYNONYM syntax:  http://technet.microsoft.com/en-us/library/ms177544(v=sql.110).aspx

sys.synonyms info:  http://msdn.microsoft.com/en-us/library/ms189458(v=sql.110).aspx

---

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Thank this author by sharing: