SYNONYM in SQL Server 2005

Microsoft introduced many additional features to SQL server 2005, one of which is SYNONYM. This article explains and illustrates the use of SYNONYMS.


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]




Fig 1.2


Conclusion


This article explained the use of SYNONYM in SQL Server 2005, which helps to cut down on the typing of four, three, or two part names, and illustrated how to use SYNONYM for SQL Server tables and stored procedures with examples.


» See All Articles by Columnist MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles