Synonyms

Hi,

In SQL Server 2005, a new feature called Synonyms was introduced. Let's discuss about it today.

SQL Server 2005 has introduced synonyms which enables the reference of another object (View, Table, Stored Procedure or Function) potentially on a different server, database or schema in our environment. This means that the original object that is referenced in all of our code is really using a completely different underlying object, but no coding changes are necessary. Think of this as an alias as a means to simplify migrations and application testing without the need to make any dependent coding changes.

Create a synonym via SQL Server 2005 Management Studio

To access the synonym interface navigate to SQL Server 2005 Management Studio => Server Name => Databases => Database Name => Synonyms => Right click on the Synonyms folder => New Synonym

In General Tab - Key in Synonym name which should be a new name in the schema and information for the aliased object

In Permissions Tab - Click the 'Add' button to grant rights to the synonym for users

In Extended Properties Tab - Add any extended properties for documentation purposes.

How to add Synonyms using T-SQL

USE [NorthWind]
GO

CREATE SYNONYM [dbo].[Empls]
FOR [WSNG1110065709\SQLEXPRESS].[Northwind].[dbo].[Employees]
GO
EXEC sys.sp_addextendedproperty @name=N'Description',
@value=N'This is a synonym referencing the Northwind database ',
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'SYNONYM',@level1name=N'Empls'

Let's validate this:

select * from [dbo].[Empls]

EmployeeID LastName FirstName Title
1 Davolio Nancy Sales Representative
2 Fuller Andrew Vice President, Sales
3 Leverling Janet Sales Representative

Let's Insert data using Synonym:

Insert into [dbo].[Empls]
( LastName, FirstName, Title)
values ('Thru Synonym', 'Synonym','Synonym')

Let's see data through Synonym:

select * from [dbo].[Empls]

Let's see data directly from table:

select * from Employees

EmployeeID LastName FirstName Title TitleOfCourtesy
7 King Robert Sales Representative Mr.
8 Callahan Laura Inside Sales Coordinator Ms.
9 Dodsworth Anne Sales Representative Ms.
11 Thru Synonym Synonym Synonym NULL

Do let me know if there is any concern.
Happy SQL Coding..