In this post i will discuss what is Synonym in SQL Server and what are the benefits or advantages of Synonym in SQL Server
Synonym is an alternate name or an alias given to an object in SQL Server. Here objects means tables, views, stored procedures and functions etc. in SQL Server.
Benefits or use of using Synonym
Here I am writing two advantages of Synonyms
1) Synonyms can be used to refer remote objects as if they are locally created.
Assume that you need to refer a table named Student present in remote server named ServerA under database named DBName. You are using ServerB and there is a LINKED SERVER created on ServerB to ServerA.
Then you will write the following script to refer Student table
select * from ServerA.DBName.dbo.Student — NEED TO WRITE FOUR PART NAME
But instead you can create synonym to refer remote table Student
create synonym Remote_Student for ServerA.DBName.dbo.Student
Now you can write
select * from Remote_Student –YOU DON’T NEED TO WRITE FOUR PART NAME
2) During the object rename you can use synonym.
Assume that i want to rename a table named ‘SlaesTerritory’ (having wrong spellings) to SalesTerritory (correct spellings). But the table SlaesTerritory is used by other objects like stored procedures and views etc.. in their T-SQL scripts.
If I rename the table then all the objects referring the table will fail to run because they have dependency on this table.
I will solve this issue by first renaming SlaesTerritory table to SalesTerritory and then by creating a synonym named SlaesTerritory for SalesTerritory(renamed) table.
It means that I have renamed the table to the correct spellings and also I don’t need to modify the T-SQL scripts of other objects referring this table because for those objects I have created a synonym with the name SlaesTerritory(which was the old name of the table)
exec sp_rename ‘SlaesTerritory’,‘SalesTerritory’–RENAME THE TABLE
create synonym SlaesTerritory for SalesTerritory –CREATE SYNONYM WITH OLD TABLE NAME
The best way to execute the command in a transaction so that both the changes must happen as a single unit.
exec sp_rename ‘SlaesTerritory’,‘SalesTerritory’
create synonym SlaesTerritory for SalesTerritory
Thats all about Synonym in SQL Server and its benefits.