Encryption in SQL Server  :-

  • If  view created with encryption then view defination will be hidden in information_schema views table.

Example  :-  create view v8 with encryption as select empno, ename, sal from emp


                         Select * from information_schema.views.

Drop views  in SQL:

Drop view v8

  • If drop the views ,it’s not effected in the base table,only deleted from information_schema views

Note  :–      (I) If Base table is dropped, so views is not dropped. Because view is an independent object.

                      (II) But in case, the view is not accessed.

Schema Binding  in SQL:-

  • When view created by schema binding then view becomes dependent object on base table.
  • The base table can’t be dropped without dropping view.

Example  :- Create view v8 with schema binding as select empno,ename,sal,from dbo.emp

Note  :–   When schema binding is used then table name prefixed by user name. Example :- dbo.emp

Example  :-

  •  Drop table emp
  •          ↓
  • Invalid because view is dependent obj,so we first drop the view in this Case.
  •          ↓
  • Drop view v8
  •           ↓
  • Drop table emp.

Synonym  in SQL  :–

  • A synonym is another name(or) alternative name for a table.
  • Synonyms are created

(i) If table name is lengthy.

(ii) To access table without owner name.

Syntax  :-

  •                  Create synonyms <name> for <tabname>

Example  :-

  •                  Create synonym s1 for emp.
  •                   Select * from s1.

DBO user                                                  NIT   user

      ↓                                                                             ↓

 Table name                                                  Table name

       ↓                                                                            ↓

    Emp                                                                  Emp

       ↓                                                                            ↓

Grant select,insert on emp to NIT  (→)  Select * from dbo.emp


                                                                         Create synonym s1 for dbo.emp


{here,to access table without owner name}  ←   select * from s1

Difference between View and Synonym :-

                   View                                                                                  Synonym

View is a subset of a table.                                         Synonym is a mirror of a table.

View can be based on more than one table        Synonym is always based on one table.                       

Dropping Synonym in SQL :-

  • Drop synonym s1.