Page 75 - DCAP508_DATABASE_ADMINISTRATION

Unit 5: SQL Server Databases

A synonym is a database object that serves the following purposes: Notes
Provides an alternative name for another database object, referred to as the base object,
that can exist on a local or remote server.

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 the AdventureWorks2008R2 sample database,
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. Adventure Works. Human Resources.
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 the synonym with the same name, but point the synonym to the new location of Employee.

5.9.1 Syntax for Creating Synonym

CREATE SYNONYM [ schema_name_1. ] synonym_name FOR <object>

<object> :: =
{
[ server_name.[ database_name ] . [ schema_name_2 ].| database_name . [ schema_name_2 ].|
schema_name_2. ] object_name
}

5.9.2 Arguments

schema_name_1
Specifies the schema in which the synonym is created. If schema is not specified, SQL
Server 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.

LOVELY PROFESSIONAL UNIVERSITY 69