Friday, August 12, 2005

Synonyms in SQL Server 2005

Creating aliases for our objects is called Synonyms. Using Synonyms we can create alias for objects,

1. existing in another server.
2. which exist in another database within the same server.
3. which exist in another schema.

Let us see a basic example of these here:

Example for point 1:

Create Synonym testSynonym for [YourServerName].YourDatabaseName.dbo.YourTableName
Select Field1, Field2 from testSynonym;

Example for point 2:

Create Synonym testSynonym_AnotherDatabase For Northwind.dbo.categories

I haven't checked any sample for point 3 yet.

In the above example I have used the table "Categories" from Northwind database. Lets assume that I use a table name which doesn't exist at all (say, "tblVadivel") still the Synonym would be created !!!

Confused :) It works like this ... "the actual check of whether the object we used exists or not is happening on the runtime and not on the design time". Hope I have made the point clear!

Objects which can be used while creating a Synonyms are listed below for the sake of completion:

1. Tables
3. Stored Procedure
4. User Defined Function
5. # and ## tables

Clean up:

Drop Synonym Name_Of_Your_Synonym

Technorati tags: , , ,

No comments: