SCHEMABINDING a VIEW

Without Schemabinding:

          If we create a view without schemabinding, then we can drop the tables which are used in the view. If we drop the table(s) then view will not work.

Example:
     Employee table script

     View creation script:

CREATE VIEW VW_EMPLOYEE

 AS

 SELECT

   ID

   ,FirstName

   ,LastName

   ,Gender

   ,Designation

   ,ManagerID

   ,Dept_ID

       FROM EMPLOYEE

     We can drop the table which is used in the view.

DROP TABLE EMPLOYEE

     If we try to select the view then it will through error.

With Schemabinding:

          If we create a view without schemabinding, then we cannot drop the tables which are used in the view. If we try to drop the table(s) then it will throw error.

Example:
     Employee table script

     View creation script:

CREATE VIEW VW_EMPLOYEE1

WITH SCHEMABINDING

AS

 SELECT

   ID

   ,FirstName

   ,LastName

   ,Gender

   ,Designation

   ,ManagerID

   ,Dept_ID

       FROM DBO.EMPLOYEE

     We canot drop the table which is used in the view.
       DROP TABLE EMPLOYEE