Synonyms (alias) in SQL Server 2005:

Sometime you might get tired by writing very big table name with its schema name. Sometime people have to use four part table name and it become tedious for developer when he uses it many time in his/her code. Synonyms object in SQL Server 2005 is a solution of that. Synonyms is not only alias but it’s an abstraction layer which will protect you from alteration in your base object. Before we start using Synonyms let me tell you, you can define synonyms on only few object described below.

CLR SP
CLR Function
Table
View
SP
Extended SP

There is also one more limitation, you can’t use Synonyms for other task then described below.

DELETE
INSERT
SELECT
UPDATE
EXECUTE

Now let me show you how you can create Synonyms for already exist object and use it in your code.

I guess you have adventureworks database which ships with SQL Server 2005 and also have table “Purchasing.PurchaseOrderHeader” in AdventureWorks database.

--Creating sysnomym

CREATE SYNONYM PO

FOR Purchasing.PurchaseOrderHeader

--accessing data with synonym

SELECT * FROM PO

Once you finish your work with Synonym and want to DROP it, you can follow below given T-SQL code.

--drop synonym

DROP SYNONYM PO

Reference: Ritesh Shah