It is quite possible situation in our projects where we can have our application spanned 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, which may be on the same or another server. So we might end up with a query with something like the below :
SELECT * FROM Server_Name.Database_Name.Schema_Name.Table_Name
This might sound fine for single query use but the actual issues arise when the location of the database server changes and you will have to search the query and change it accordingly. Now if there are too much queries like these, then search all of them and change them. So this becomes quite problematic and even if single one is missed, the application may crash. To avoid such kind of situations, SQL server provides the concept of Synonyms. They are created in the database which needs 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 be having 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 which it is to be created in the current database i.e. 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 same server, so we will leave this empty. Further select the target database i.e DB2 and schema name in which the table is.
Next, we select the object type, which we would like to be accessed using 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 we can 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 database, you know exactly where you will be required to make the changes. So this was about the use of synonyms. Hope you enjoyed reading it…!!!