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.
