How to Create VIEW With SCHEMABINDING in SQL
In this article I am going to explain how to create VIEW With SCHEMABINDING in SQL
Introduction
View in SQL is a virtual table and this table
is created on the result set of SQL statement. View is similar to a normal table
and also have rows and columns. View may be combination of some rows and
columns of two or more than two tables. You can use SQL function and WHERE
clause with View in SQL statement.
View works on tables stored in database.
If someone delete table then view doesn't work. You can restrict these tables
that no one delete these table before deletion of view. For this we create view
with SCHEMA BINDING in SQL. To create view we use CREATE VIEW With SCHEMABINDING
statement.
A Statement that create vendors table
|
create table vendors
(
vendorid int,
vendorname varchar(15),
vendorcity varchar(15),
vendorstate varchar(15)
) |
Statements that insert data in vendors table
|
insert into vendors
values (20,'vipendra','noida','up')
insert into vendors
values (21,'deepak','lucknow','up')
insert into vendors
values (22,'rahul','kanpur','up')
insert into vendors
values (23,'malay','delhi','delhi')
insert into vendors
values (24,'mayank','noida','up') |
A Statement that is used to fetch data from
vendors table
A Statement that create mcninvoices table
|
create table mcninvoices
(
invoiceid
int not null identity primary key,
vendorid
int not null,
invoiceno
varchar(15),
invoicetotal
money,
paymenttotal
money,
creadittotal
money
) |
Statements that insert data in mcninvoices table
|
insert into mcninvoices
values (20,'e001',100,100,0.00)
insert into mcninvoices
values (21,'e002',200,200,0.00)
insert into mcninvoices
values (22,'e003',500,0.00,100)
insert into mcninvoices
values (23,'e004',1000,100,100)
insert into mcninvoices
values (24,'e005',1200,200,500) |
A Statement that is used to fetch data from
mcninvoices table
A Statement that is used to create a view
with schemabinding in SQL
|
create view viewschema
with schemabinding
as
select
vendorname as name,invoicetotal
as invoice_total
from
dbo.mcninvoices
join dbo.vendors
on
mcninvoices.vendorid=vendors.vendorid |
A Statement that show data
in view
