Creating Views with Scheme Binding in Sql Server

1. Lets create tables first which we will be using in the view. Here I'm using two tables employee and car.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employee] (
 [EmpDeptID] [int] IDENTITY(1, 1) NOT NULL
 ,[EMpName] [varchar](100) NULL
 ,[salary] [numeric](18, 0) NULL
 ) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[Employee] ON
GO

INSERT [dbo].[Employee] (
 [EmpDeptID]
 ,[EMpName]
 ,[salary]
 )
VALUES (
 1
 ,N'programming'
 ,CAST(1 AS NUMERIC(18, 0))
 )
GO

INSERT [dbo].[Employee] ([EmpDeptID],[EMpName],[salary])
VALUES (3,N'Mary',CAST(5000 AS NUMERIC(18, 0)))
GO

INSERT [dbo].[Employee] ([EmpDeptID],[EMpName],[salary])
VALUES (4,N'Anthony',CAST(500 AS NUMERIC(18, 0)))
GO

INSERT [dbo].[Employee] ([EmpDeptID],[EMpName],[salary])
VALUES (5,N'jacob1',CAST(555 AS NUMERIC(18, 0)))
GO

INSERT [dbo].[Employee] ([EmpDeptID],[EMpName],[salary])
VALUES (8,N'Peter',CAST(4500 AS NUMERIC(18, 0)))
GO

INSERT [dbo].[Employee] ([EmpDeptID],[EMpName],[salary])
VALUES (9,N'chandan',CAST(500 AS NUMERIC(18, 0)))
GO

SET IDENTITY_INSERT [dbo].[Employee] OFF
GO
--------------------------------------------------------

CREATE TABLE Car (carid INT,carname VARCHAR(100))
GO

INSERT INTO car
VALUES (1,'BMW')
GO

INSERT INTO car
VALUES (2,'FORD')
GO

INSERT INTO car
VALUES (3,'MAZDA')
GO

INSERT INTO car
VALUES (4,'Ferrari')
GO

2. Now I will create a Simple View without using scheme binding using this tables

CREATE VIEW [CARVIEW]
AS
SELECT *
FROM employee emp
INNER JOIN car c ON emp.empdeptid = c.carid

-------------------------------------------------------------------------
SELECT * FROM carview

3. Now Drop the table car and now run the view. The view gives error as the table car contained in it has been dropped now. Thus the view gets broken.

Hence to avoid such error we use View with scheme binding.
Thus it ensures the view created would not get broken in future and also would not allow any modifications to the tables it is using withing the view like renaming column name, delete column etc.

4. Recreate the Car table and now we will  Create view with scheme binding. After creating the view we will again try to drop the table car.

ALTER VIEW DBO.[CARVIEW]
 WITH SCHEMABINDING
AS
SELECT EMP.EMPDEPTID
 ,EMP.EMPNAME
 ,EMP.SALARY
 ,C.CARID
 ,C.CARNAME
FROM DBO.EMPLOYEE EMP
INNER JOIN DBO.CAR C ON EMP.EMPDEPTID = C.CARID

-----------------------------------------------
SELECT *
FROM DBO.[CARVIEW]

5. Now when we execute query to drop the table car it throws a exception that table cannot be dropped as it is referenced by one of the objects. Thus Views with Schema Binding would not get broken.