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:

  1. CREATE SYNONYM schema_name.synonym_name FOR object_name  
Example
  1. CREATE DATABASE DEMOS  
  1. USE DEMOS  
CREATE EMP TABLE
  1. CREATE TABLE DBO.EMP  
  2. (  
  3. ID INT IDENTITY(1,1) PRIMARY KEY,  
  4. FIRTSNAME VARCHAR(100) ,  
  5. LASTNAME VARCHAR(100),  
  6. LOCATION VARCHAR(100),  
  7. DOB DATETIME,  
  8. SALARY MONEY,  
  9. DEPT INT   
  10. )  
INSERT SOME DATA TO EMP TABLE
  1. INSERT INTO DBO.EMP(FIRTSNAME,LASTNAME,LOCATION,DOB,SALARY,DEPT)  
  2. VALUES  
  3. ('RAKESH','KALLURI','HYDERABAD','07-23-1989',24000,1),  
  4. ('NARESH','CH','PUNE','07-23-1987',48000,1),  
  5. ('SRUJAN','KUMAR','HYDERABAD','07-23-1988',25000,1),  
  6. ('VENKATESH','BODUPPALY','HYDERABAD','07-23-1986',32000,2),  
  7. ('ALI','MD','HYDERABAD','07-23-1987',38000,2),  
  8. ('GANGA','RAJAYAM','PUNE','05-26-1987',390000,2),  
  9. ('RAVI','KUMAR','CHENNAI','03-23-1986',47000,1),  
  10. ('PRAVEEN','KUMAR','DELHI','07-23-1988',33000,2)  
CREATE SYNONM FOR EMP TABLE
  1. 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

  1. SELECT * FROM DBO.SY_EMP  
INSERTING DATA USING SYNONYM
  1. INSERT INTO DBO.SY_EMP(FIRTSNAME,LASTNAME,LOCATION,DOB,SALARY,DEPT)  
  2. VALUES  
  3. ('RAMU','J','HYDERABAD','07-23-1989',24000,1)  
DELETE RECORD FROM SYNONYM
  1. 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
  1. CREATE SYNONYM SY_OTHERDB_TABLE FOR DEMOS1.DBO.EMP  
Dropping a synonym
  1. DROP SYNONYM  
  2. 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

  1. 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

  1. EXEC SP_HELPTEXT SY_EMP 

Figure 6: Metadata Information