I am not talking about words that have the same meaning. I am talking about the SQL Server object that allows you to create an alternate name for a database object. The subject came up on a discussion forum that was connected to an article about using custom schemas. I thought it would be a good topic to bring into the light today.
First, if you are not familiar with synonyms in SQL Server, you can get a good overview and a general understanding from an article that Jason Shadonix wrote a while back. I don’t want to rehash Jason’s article, but synonyms can be very helpful in certain situations. I wrote an article recently about the importance of a consistent server setup.
In that article I advocate for database names to be named the same across prod, test, and dev. Still, some shops have a need or choose to use a _test or _dev appended to their production database name in their test or dev environments. In those cases, synonyms can really help. When you need to access data tables, views, stored procs, functions, on different databases or linked servers, you can create a synonym. This ensures that your queries and stored proc calls don’t have to change when you promote your code between dev, test and production.
Of course, you should be careful in how you use them. In some cases, it might be appropriate to name the synonym in such a way for it to be obvious to others that it is not the actual SQL object. Perhaps adding "S" to the front of the original object name when you create the synonym, for example. In other cases, you might want to obfuscate the actual underlying object with a synonym. In any case, synonyms are a useful tool for the SQL professional to be aware of.
If you haven’t used or heard of synonyms before, click a few links. Do a few Google searches and learn about them. They have the potential to help you save some time and they might make your life easier when it comes to promoting code to different environments.
Do you use synonyms in your work place? If you do, share how you use them and any issues you have run across with them in your systems?