what is synonym in sql server 2005 / 2008

Synonym is one of the best thing introduced in SQL 2005 and continue with SQL 2008. A synonym allows you to create alternative names for objects inside of the database. If an object is renamed, or the schema of an object is changed, a synonym can allow existing applications to continue to use the old names.

Synonyms can also reference objects in different databases, or even different servers, by using three-part or four-part object names. A synonym must reference a database object, and not another synonym. Multiple names can be created for a single database object, so long as they all refer directly to the database object.

While synonyms provide alternative names, permissions are still maintained at the database object level. A user can be granted access to a synonym, but unless the required access to the underlying object is available, statements using that synonym will fail.

Create Synonym: Creating a synonym can be accomplished using following Transact-SQL statements:

CREATE SYNONYM yourSynonym FOR  [[[server.][database].][schema_name].]object_name

The following is an example of creating an alias in the dbo schema for the dbo.Employees table in the Training database:

--create synonym

CREATE SYNONYM dbo.sEmployees FOR dbo.Employees;

--select using synonym

select * from sEmployees

--create synonym

CREATE SYNONYM dbo.sEmployees1 FOR MyServer.Trainig.dbo.Employees;

If you want to change a synonym, there is no ALTER command to do it. You need to DROP and CREATE it again with new context. Here is a sample:

-- Drop synonym MyProduct.

IF  EXISTS (SELECT * FROM sys.synonyms WHERE name = 'sEmployees')

DROP SYNONYM dbo.sEmployees;

You will find synonyms to be useful in many situations, including when:

·          Objects are renamed.

·          Objects are moved to a different schema.

·          Objects are moved to a separate database.

·          Objects are moved to a different server.

·          Alternative names for a database object are needed.

Synonyms can reference many types of database objects like SQL Stored Procedure, View , Table, UDF and others , and they allow flexibility in naming and location. They make database maintenance easier, and they allow a separate layer of abstraction from applications that directly access objects.

SYNONYMS can be used only to change data of object not the schema of the object. SYNONYMS can be used with only SELECT, UPDATE, INSERT, DELETE, EXECUTE commands.

SYNONYMs are loosely bound to the referenced objects. So you can delete a SYNONYM without getting any warning that it is being referenced by any other database object.

Chaining is not allowed. It means that you can not create SYNONYM of a SYNONYM.

It will consumes possible object names, as you can not create a table with the same name of a synonym.

The object for which the SYNONYM is being created is checked at run time. It is not checked at creation time. So this means that if you make any related error e.g. spelling error, the synonym will created, but you will get an error while accessing the object.

SYNONYM cannot be referenced in a DDL statement .

In order to create a synonym, you need to have CREATE SYNONYM permissions. If you are a sysadmin or db_owner you will have these privileges or you can grant this permission to other users. Also, you create a synonym that crosses databases you will need permissions for the other database as well.