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.