How to Create VIEW With SCHEMABINDING in SQL

In this article I am going to explain how to create VIEW With SCHEMABINDING in SQL
  • 4113

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

 ven (2).jpg

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

 inv111.jpg

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

5.jpg


© 2019 DotNetHeaven. All rights reserved.