SQL Server

What is Synonym in SQL Server and its benefits

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

 

Use Database_at_ServerB
go
select * from ServerA.DBName.dbo.Student — NEED TO WRITE FOUR PART NAME

But instead you can create synonym to refer remote table Student

Use Database_at_ServerB
go
create synonym Remote_Student for ServerA.DBName.dbo.Student

Now you can write

Use Database_at_ServerB
go
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)

 

Use DBName
Go

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.

 

use DBName
Go

begin try
begin transaction
exec sp_rename ‘SlaesTerritory’,‘SalesTerritory’
create synonym SlaesTerritory for SalesTerritory
commit transaction
end try
begin catch
rollback transaction
end catch

Thats all about  Synonym in SQL Server and its benefits.

3 thoughts on “What is Synonym in SQL Server and its benefits

  1. Very informative article, i am regular reader of your blog.

    I noticed that your blog is outranked by many other websites in google’s search results.
    You deserve to be in top-10. I know what can help you, search in google for:

Leave a Reply