Synonyms in SQL Server

It is quite a possible situation in our projects where we can have our application span across multiple databases and even more, these databases could be located on different servers. So with that comes the requirement to exchange the data among these databases. To do that, we might be using the fully-qualified location of the table in the other database that may be on the same or another server. So we might end up with a query with something like the following:

SELECT * FROM Server_Name.Database_Name.Schema_Name.Table_Name

This might sound fine for a single query but the actual issues arise when the location of the database server changes and you will need to search the query and change it accordingly. Now if there are too many queries like these, then search all of them and change them. So this becomes quite problematic and even if a single one is missed, the application may crash. To avoid such situations, SQL Server provides the concept of Synonyms. They are created in the database that need to access the other database and use them in the SELECT statement, like we access the table in the local database. Let's discuss with an example.
 
To start with, we will be creating two sample databases DB1 and DB2. DB2 will have a table named ApplicationUsers, and DB1 will be required to access the data of this table. For this purpose, we will be creating a synonym.
 
Now our setup is complete. Since DB1 needs to access the DB2, we will create the synonym in DB1. Select Synonym node under the DB1, right-click and select Create Synonym option. This will open up the setup screen.


 
Provide a suitable name for the synonym (ours will be synm_DB2). Optionally, you can provide the schema under that it is to be created in the current database in other words DB1. Next, you can provide the server name where the target database is, if it is on a different server. In our case, it is on the same server, so we will leave this empty. Further select the target database, in other words DB2 and schema name in which the table is.


 
Next, we select the object type, that we would like to be accessed using the synonym. This could be a Table, View, Stored Procedure or Function. We will select the Table type:


 
Finally, select the name of the table on which we need to create the synonym. In our case, it is ApplicationUsers.


 
And that's it. We are done with the creation process and our synonym is ready to be used. Simply execute the following command to see the results.


 
So easy to create and use, with the big advantage that if there is any changes in the table name or the location of the database, you know exactly where you will be required to make the changes. So this was about the use of synonyms. I hope you enjoyed reading it...!!!