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.

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

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:

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:


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