synonym in SqlServer

Question

hi What is a synonym in Sql Server? Why would you want to create a synonym?

Satyapriya Nayak · Answer

Hi Shubham What is SYNONYM? SYNONYM is a single-part name that can replace a two three or four-part name in many SQL statements. Using SYNONYMS in RDBMS cuts down on typing. SYNONYMs can be created for the following objects: Table View Assembly (CLR) Stored Procedure Assembly (CLR) Table-valued Function Assembly (CLR) Scalar Function Assembly Aggregate (CLR) Aggregate Functions Replication-filter-procedure Extended Stored Procedure SQL Scalar Function SQL Table-valued Function SQL Inline-table-valued Function SQL Stored Procedure Let us assume we have two databases namely RainbowResearch and RiverResearch as shown below. USE [master] GO /****** Object: Database [RainbowResearch] Script Date: 10/01/2006 22:05:41 ******/ IF EXISTS (SELECT name FROM sys.databases WHERE name = N'RainbowResearch') DROP DATABASE [RainbowResearch] go Create Database RainbowResearch go Use RainbowResearch go USE [master] GO /****** Object: Database [RainbowResearch] Script Date: 10/01/2006 22:05:41 ******/ IF EXISTS (SELECT name FROM sys.databases WHERE name = N'RiverResearch') DROP DATABASE [RiverResearch] go Create Database RiverResearch go Use RiverResearch go Let us assume that we have the following schemas and objects on the databases RainbowResearch and RiverResearch. Use RainbowResearch go Create Schema Rain go Create Schema Snow go Create table Rainbowresearch.Rain.Cities (id int City varchar(200)) go insert into Rainbowresearch.Rain.Cities select 1 'Hongkong' insert into Rainbowresearch.Rain.Cities select 2 'Tokyo' insert into Rainbowresearch.Rain.Cities select 3 'Beijing' insert into Rainbowresearch.Rain.Cities select 4 'Taipei' insert into Rainbowresearch.Rain.Cities select 5 'Seoul' insert into Rainbowresearch.Rain.Cities select 6 'Mumbai' go Create procedure Rain.DisplayCities @id int as Select City from Rainbowresearch.Rain.Cities where id=@id go Create table Rainbowresearch.Snow.Cities (id int City varchar(200)) go insert into Rainbowresearch.Snow.Cities select 1 'Tokyo' insert into Rainbowresearch.Snow.Cities select 2 'Seoul' insert into Rainbowresearch.Snow.Cities select 3 'Moscow' insert into Rainbowresearch.Snow.Cities select 4 'NewYork' go Create procedure Snow.DisplayCities @id int as Select City from Rainbowresearch.Snow.Cities where id=@id go Use RiverResearch go Create Schema River go go Create table RiverResearch.River.Cities (id int City varchar(200)) go insert into RiverResearch.River.Cities select 1 'Hongkong' insert into RiverResearch.River.Cities select 2 'Tokyo' insert into RiverResearch.River.Cities select 3 'Beijing' insert into RiverResearch.River.Cities select 4 'Taipei' insert into RiverResearch.River.Cities select 5 'Seoul' go Create procedure River.DisplayCities @id int as Select City from RiverResearch.River.Cities where id=@id Go Now let us query the tables from all the schemas from both of the databases. select * from RiverResearch.River.Cities go select * from Rainbowresearch.Rain.Cities go select * from Rainbowresearch.Snow.Cities go You will see the following results. [Refer Fig 1.0] Fig 1.0 Assume that I want to query all the above tables in both the databases using a Single part name. In order to achieve this we are going to create SYNONYMS as shown below. use RainbowResearch go create SYNONYM RiverCities for RiverResearch.River.Cities go create SYNONYM RainCities for Rainbowresearch.Rain.Cities go create SYNONYM SnowCities for Rainbowresearch.Snow.Cities go use RiverResearch go create SYNONYM RiverCities for RiverResearch.River.Cities go create SYNONYM RainCities for Rainbowresearch.Rain.Cities go create SYNONYM SnowCities for Rainbowresearch.Snow.Cities go Now let us query using the Single part name in both databases as shown below. Use RiverResearch go Select * from RiverCities Select * from RainCities Select * from SnowCities Go Use RainbowResearch go Select * from RiverCities go Select * from RainCities go Select * from SnowCities go You will see the following results. [Refer Fig 1.1] Fig 1.1 The same SYNONYM can be created for stored procedures as well: use RainbowResearch go create SYNONYM ShowRiverCities for RiverResearch.River.DisplayCities go create SYNONYM ShowRainCities for Rainbowresearch.Rain.DisplayCities go create SYNONYM ShowSnowCities for Rainbowresearch.Snow.DisplayCities go use RiverResearch go create SYNONYM ShowRiverCities for RiverResearch.River.DisplayCities go create SYNONYM ShowRainCities for Rainbowresearch.Rain.DisplayCities go create SYNONYM ShowSnowCities for Rainbowresearch.Snow.DisplayCities go Now let us execute the stored procedures using the one part name as shown below. use RainbowResearch go exec ShowRainCities 1 go exec ShowRiverCities 3 go exec ShowSnowCities 4 go use RiverResearch go exec ShowRainCities 1 go exec ShowRiverCities 3 go exec ShowSnowCities 4 go You will see the following results. [Refer Fig 1.2] Please refer the below link http://www.databasejournal.com/features/mssql/article.php/3635426/SYNONYM-in-SQL-Server-2005.htm http://blog.sqlauthority.com/2008/01/07/sql-server-2005-introduction-and-explanation-to-synonym-helpful-t-sql-feature-for-developer/ Thanks

Maziyar Nazaryan · Answer

http://msdn.microsoft.com/en-us/library/ms187552.aspx