Synonyms In SQL Server

Synonyms is database object which can be created as an alias name/duplicate name for any object like table views ,procedure etc.

Introduction

Synonyms is a database object which can create an alias name/duplicate name for any object, like table views, procedure etc.

If we apply any DML operations on Synonyms, the same operations automatically affect the corresponding base table and vice versa.

If we create a synonym, the synonym will be created on the entire Table. It is not possible to create the synonym on a table partially.

A synonym can become invalid in two cases.

  1. When we drop the base table.
  2. When we change the base table name.

On an invalid synonym, we cannot apply any DML operations. We cannot create a synonym based on more than one table at a time.

When we change the structure of the base table, the corresponding synonym is automatically reflected with the  same change.

Syntax

Create synonym <synonym name> from <TN/Object name>

Example - Create synonym syn1 from employee

To execute the above query, it will create a synonym syn1 on Employee Table for accessing the data using DML opertaions.

Importnat point About Synonyms

  1. Generally, the Synonyms are used in remote enviroment for accessing the data from mutiple locations.
  2. Synonyms will provide security for original Table Names in Database
ViewsSynonyms
It is a virtual or logical Table of the base TableIt is an Alias Names of the Base Table
It doesn’t contain physical existence and it doesn’t occupy memoryIt contains physical existence and occupy memory
It can be created on multiple Tables at a timeIt will create on a single Table only
It will provide the security to the original Table Names and also Data.It will provide security for the original Table Names only but not data.
By using view we can hide or restrict the specific rows and columns in the Table