SYNONYM in SQL Server 2005

October 4, 2006

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]

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]

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]

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