Synonyms are a great SQL Server feature which first appeared with SQL Server 2005. They are essentially aliases for tables and other objects that exist in databases outside of the one you’re coding in. They allow you to streamline your code, because you can use a short synonym in place of a long, four-part object name for those foreign objects. Instead of typing something like...
...you can create a synonym to stand for the long table definition, and your code will look like you’re calling a local table, e.g. :
This brevity is especially nice when you’re joining a foreign table into a complex query.
The thing I like most about them is that they allow me to keep all my references to outside things in the same place, the synonym “folder”. If the database I’m referring to moves or changes name I can easily update a few links to keep things together, rather than updating 50 queries.
This was very helpful when I wanted to create a development environment, a testing environment and a production environment for a new system I was working on. Each environment had two databases in it, a “parent” and “child”. To have the child in each environment point to the correct parent, I just needed to update the synonyms in the child, and presto!
There are two ways to create synonyms. You can go into the Management Studio, find your database in the explorer window. Under it, next the Tables node is a node named “Synonyms”. You can right click on it and select “New Synonym”. A wizard will walk you through the steps. There’s also a T-SQL command to create them, a la:
CREATE SYNONYM synLocation
Thanks to Pinal Dave for this example!