How to Use Synonyms in sql server
First question comes in the mind, what are synonyms and how to use synonyms in sql server? Well in simple words we can say it is alias for referring remote objects which is on different server and location
If I want to some data from different server where you don’t have any control on database objects. I use to execute select statement like this
Select * from remoteserver.remotedbname.schema.tablename
Now using synonyms you avoid writing remote server name, database name, schema and tablename. You can use single synonym name to extract data from remote server as shown below.
* Create synonym as shown below
Create synonym clienttable
* Alter the synonym as shown below
Use tesdb Go
Alter synonym clienttable
For newremoteserver.newremotedbname.schema.newtablename Go
* Drop Synonyms as shown below :
DROP SYNONYM clienttable
Now if need to extract data from remote server, I will write select query using above synonym name. This will give use the same result and as above select query without using synonym.
Select * from clienttable
While writing stored procedure sometimes we use to refer remote database objects. Every time when table/database or server name changes than you have to make changes to your stored procedures. If you are using synonym name than just alter the synonym with new table name, database and server name. No need to make any changes in stored procedure if you are using synonym name in the sql code.
Synonym can be created on following objects.
• Stored procedure
• Extended stored procedure
By using SYNONYMS can modify the data of object not the schema of the object. SYNONYMS will help you to execute DML statement as well as SELECT, EXECUTE commands.