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.
There is also one more limitation, you can’t use Synonyms for other task then described below.
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.
CREATE SYNONYM PO
–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 PO
Reference: Ritesh Shah