Synonym in SQL Server

A synonym is nothing but a name. It does not store any data or any T-SQL Query. It just refers to the database objects. Here the database objects include the following:

1. Tables
2. Views
3. Functions
4. Procedures
5. CLR Assemblies

The following is the basic syntax for creating a synonym:

CREATE SYNONYM schema_name.synonym_name FOR object_name

Example

CREATE DATABASE DEMOS

USE DEMOS

CREATE EMP TABLE

CREATE TABLE DBO.EMP
(
ID INT IDENTITY(1,1) PRIMARY KEY,
FIRTSNAME VARCHAR(100) ,
LASTNAME VARCHAR(100),
LOCATION VARCHAR(100),
DOB DATETIME,
SALARY MONEY,
DEPT INT
)

INSERT SOME DATA TO EMP TABLE

INSERT INTO DBO.EMP(FIRTSNAME,LASTNAME,LOCATION,DOB,SALARY,DEPT)
VALUES
('RAKESH','KALLURI','HYDERABAD','07-23-1989',24000,1),
('NARESH','CH','PUNE','07-23-1987',48000,1),
('SRUJAN','KUMAR','HYDERABAD','07-23-1988',25000,1),
('VENKATESH','BODUPPALY','HYDERABAD','07-23-1986',32000,2),
('ALI','MD','HYDERABAD','07-23-1987',38000,2),
('GANGA','RAJAYAM','PUNE','05-26-1987',390000,2),
('RAVI','KUMAR','CHENNAI','03-23-1986',47000,1),
('PRAVEEN','KUMAR','DELHI','07-23-1988',33000,2)

CREATE SYNONM FOR EMP TABLE

CREATE SYNONYM DBO.SY_EMP FOR DBO.EMP

Figure 1: Create Synonym

Once a synonym is created we can use that synonym for what it stands for, in other words in the preceding we created the synonym for the EMP table. In this case we can use T-SQL statements like SELECT, INSERT, UPDATE and DELETE.

SELECTING DATA FROM SYNONYM

SELECT * FROM DBO.SY_EMP

INSERTING DATA USING SYNONYM

INSERT INTO DBO.SY_EMP(FIRTSNAME,LASTNAME,LOCATION,DOB,SALARY,DEPT)
VALUES
('RAMU','J','HYDERABAD','07-23-1989',24000,1)

DELETE RECORD FROM SYNONYM

DELETE FROM DBO.SY_EMP WHERE ID=3

Figure 2: Select Synonym

A synonym can refer to other database objects also. In case we are working on one database and we need to get data from another database also. Either within the server or from another server using a linked server.

The developer or user does not always need to remember the database name or schema name, It is sometimes difficult to remember.



Figure 3: Accesing some object

CREATE SYNONYM FOR OTHER DATABASE OBJECTS

CREATE SYNONYM SY_OTHERDB_TABLE FOR DEMOS1.DBO.EMP

Dropping a synonym

DROP SYNONYM
DROP SYNONYM DBO.SY_EMP

Note

Alter synonym is not supported in SQL Server. We must always drop and re-create.

Synonyms can reference Non-existing objects

We can create a synonym even if the object referenced does not exist.

CREATE SYNONM FOR NON-EXISTING EMP TABLE

CREATE SYNONYM DBO.SY_NON_EXISTING_EMP FOR DBO.NO_EMP_EXISTS

Figure 4: Success

When we run the preceding code the command is completed successfully, but the referenced object does not exist in the database. This is because of the late-binding behavior of synonyms. The advantage of synonyms is that we can use a single synonym for many objects, in other words in the preceding example we created a synonym for a non-existing object later and we create that object name with either a table object, view object, function object and procedure object and so on.

SELECTING DATA FROM SY_NON_EXISTING_EMP

SELECT * FROM DBO.SY_NON_EXISTING_EMP

Figure 5:Non Existing Emp

When we select run the preceding query we get an error, because the referenced object does not exist in the database.

The following is the differences between a synonym and other objects:

1. A synonym can reference only one database object, but a view can reference multiple objects (tables using joins).
2. A synonym cannot support the WITH SCHEMABINDING option compared to other objects like functions, views and procedures. Whenever we try to alter or drop base objects it does not give a error, because of late-binding behavior.
3. A synonym cannot reference another synonym object.
4. It does not support exposed the metadata information. For example:

GETTING METADATA INFROMATION

EXEC SP_HELPTEXT SY_EMP

Figure 6: Metadata Information