How to Use Synonyms in sql server

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

Use testdb
Create synonym clienttable
For remoteserver.remotedbname.schema.tablename

* Alter the synonym as shown below

Use tesdb Go

Alter synonym clienttable
For newremoteserver.newremotedbname.schema.newtablename Go

* Drop Synonyms as shown below :

Use testdb
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.
• Table
• Stored procedure
• Extended stored procedure
• Function
• Views
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.

Leave a Reply




+ 1 = 8