SQL with Manoj

A SYNONYM is a database object that serves the following purposes:

– It provides an alternative name for another database object, referred to as the base object, that can exist on a local or remote server.
– It provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.

For example: consider the Employee table of Adventure Works, located on a server named Server1. To reference this table from another server, Server2, a client application would have to use the four-part name, Server1.AdventureWorks.Person.Employee. Also, if the location of the table were to change, for example, to another server, the client application would have to be modified to reflect that change.

To address both these issues, you can create a synonym, EmpTable, on Server2 for the Employee table on Server1. Now, the client application only has to use the single-part name, EmpTable, to reference the Employee table. Also, if the location of the Employee table changes, you will have to modify the synonym, EmpTable, to point to the new location of the Employee table. Because there is no ALTER SYNONYM statement, you first have to drop the synonym, EmpTable, and then re-create it with the same name, but point it to the new location of Employee.

CREATE SYNONYM:
Syntax-
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR :: = {
[ server_name.[ database_name ] . [ schema_name_2 ]. database_name .
[ schema_name_2 ]. schema_name_2. ] object_name
}

Arguments:
schema_name_1 : Specifies the schema in which the synonym is created. If schema is not specified, SQL Server 2005 uses the default schema of the current user.
synonym_name : Is the name of the new synonym.
server_name : Is the name of the server on which base object is located.
database_name : Is the name of the database in which the base object is located. If database_name is not specified, the name of the current database is used.
schema_name_2 : Is the name of the schema of the base object. If schema_name is not specified the default schema of the current user is used.
object_name : Is the name of the base object that the synonym references.

Example:

-- Create a synonym for the Product table in AdventureWorks.
CREATE SYNONYM MyProduct
FOR AdventureWorks.Production.Product

-- Query the Product table by using the synonym.
SELECT ProductID, Name
FROM MyProduct
WHERE ProductID = 5