How to Create a View with Schema Binding in SQL Server

Creating a View with SchemaBinding in SQL Server
The SHEMABINDING is essentially takes the things that your VIEW is depend upon(Tables/other Views), and "Binds" them to that View. The significance of this is that, no one can make alteration(ALTER/DROP) to those underlying objects, unless you drop the Schema-Bound View first.

Notes:
It prevents your VIEW from becoming impact by alterations made to the underlying objects.
If you want to create an Index on your View, you must create it with using the " WITH SCHEMEBINDING" Option.
If you want to create a Schema-Bound user defined Function that references to your View then your View must also be " SCHEMEBINDING".
--------------------------------------------------
Example :
Creating a View with SchemaBinding Option

USE [TAMATAM]
GO

CREATE VIEW vw_EmpDeptDtls
WITH SCHEMABINDING
AS
SELECT E.[Emp_Id],
  E.[Emp_Name],
  E.[Gender], 
  E.[Joining_Date],
  E.[Basic_Sal],
  D.[Dept_Id],
  D.[Dept_Name],
  D.[Bonus_Rate],
  J.[Job_Id],
  J.[Job_Title]
  FROM [dbo].[Emp_Test] E Inner Join [dbo].[Dept] D
  ON E.Dept_Id=D.Dept_Id
    Inner Join [dbo].[JobTitles] J
  ON E.Job_Id=J.Job_Id
GO
--------------------------------------------------
Now you can create an Index on the above View to make the Query execution faster :

Create Unique Clustered Index Ind_Vw_EmpDept
On vw_EmpDeptDtls (Emp_Id,Dept_Id,Job_Id)
--------------------------------------------------

Notes :

Now if you try to make any alterations to the underlying objects...

--ALTER TABLE EMP_TEST Drop Column [Job_Id]
--ALTER TABLE EMP_TEST ALTER Column [Emp_Name] Varchar(250)
--Drop Table [Emp_Test]

You will get the following errors...

--The object 'vw_EmpDeptDtls' is dependent on column 'Job_Id'

--ALTER TABLE DROP COLUMN Job_Id failed because one or more objects access this column.

--Cannot DROP TABLE 'Emp_Test' because it is being referenced by object 'vw_EmpDeptDtls'

Please note that :

--You can Alter the underlying objects of the View, by removing the SchemaBinding by altering the View .

--You can drop/alter a Column from the base table, which is not used in the View.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------