Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 4, 2006

SYNONYM in SQL Server 2005

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM