Pages

Sunday, August 3, 2008

What is Synonyms in SQL Server 2005?

Before starting what is Synonym, let us consider the following scenarios...
Scenario - 1
When we want to access data from a different database, we will write some thing like
SELECT * FROM Schema_Name.dbo.Table_Name
We may be using the above statement in lots of procedures/functins/triggers.
When we want to move our to code different server(may be from Dev to QA/Prod)
again we have to go and change all the statement by replaceing Dev Schema_Name with the QA or Prod Schema Name.
May be as a developer we may felt that how can we change in all the place by changing at one location.

Scenario-2
There might be lots of places where we don't want to expose table names to the other users.
Currently in 2000 we have to create a view irrespective of any where clause.

like this there might be lots of scenarios...:)

To solve all these, we will use SYNONYMs.
Now we will see what is a SYNONYM:
A synonym is just an alternate name for an existing database object that keeps a database user (more likely, a database programmer) from having to use a multipart name for an object.
Synonyms can be defined on a two-part, three-part, or four-part SQL Server object name.
Here is the Syntax for creating a SYNONYM

CREATE SYNONYM [schema_name.]synonym_name FOR object_name

Example:
CREATE SYNONYM emp_syn FOR TestDB.dbo.emp

Now we can access the data from the emp table by using the synonym as follows:
SELECT * FROM emp_syn
A synonym can be defined by the following database objects:

Table
View
Stored procedure
User-defined function
Extend stored procedure
Replication filter procedure
CLR stored procedures
CLR functions

Although synonyms can be created on a multipart object name, they are scoped to the database that they are created.

SYNONYMs can be droped as similar to the droping any other object. Here is the syntax:

DROP SYNONYM

There is no ALTER statement for Synonym.
In order to change the SYNONYM we have drop and recreate it again.