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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: