I have already discussed about View in my previous post, In this article I will discuss about different types of view.
In SQL Server Views has been divided into three categories
1- Standard Views
In this article we will explore
Standard Views, To know more about Index Views and Partitioned view click on above links.
Standard Views
We can create view on single
table or combination on multiple tables. When we create a view on a single
table without using any aggregated function then we call it as Simple View, when we
create a view on combination of multiple tables or if any aggregated function
(i.e GROUP BY clause, HAVING Clause, Distinct Functions, Group Functions,
Functions call) is used on single table then we call it as Complex View.
We can perform DML Operation on Simple view but all Not Null columns must
be present in DML command and in View. This means Simple Views are Updatable Views; we will explore this later with a simple
example.
We can’t perform DML Operation on Complex view by
default; this means Complex Views are Non Updatable
Views. We can make complex view updatable with the help of Instead of Trigger.
We will explore this with a simple example in this article.
Now we can say Standard Views
are of two types.
1- Simple View (Updatable View)
2- Complex View (Non Updatable View)
Simple View (Updatable View) explanation with example
Let’s create a simple view and
perform any DML operation
/*I AM USING MY OLD DATABASE,
YOU CAN USE YOUR DATABASE OTHERWISE YOU CAN CREATE NEW DATABASE NAME AS
TEACHMESQLSERVER USING THE BELOW COMMAND*/
Create DATABASE TEACHMESQLSERVER
GO
Use TEACHMESQLSERVER
Go
/*CREATING TWO TABLES */
Create table DEP (DEPID int primary key,DEPNAME Varchar(max))
go
create table EMP (EMPID int primary key, EMPNAME varchar(max),DEPID int foreign key references Dep(DEPID))
GO
/*INSERTING DATA INTO BOTH
TABLES */
INSERT INTO DEP VALUES(10,'HR'),(11,'SALES'),(12,'IT')
GO
INSERT INTO EMP VALUES(1,'GHANESH',10),(2,'PRASAD',11),(3,'GAUTAM',10),(4,'ANVIE',12),(5,'OJAL',12)
GO
/*CREATING A SIMPLE VIEW*/
CREATE VIEW SIMPLEVIEW
AS
SELECT DEPID,DEPNAME FROM DEP
GO
/*REFERENCING VIEW TO FETCH
DATA */
SELECT * FROM SIMPLEVIEW
You have successfully created a
simple view now let’s perform any DML Operation on SimpleView.
/*DML OPERATION PERFORMING ON
SIMPLE VIEW*/
GO
INSERT INTO SIMPLEVIEW VALUES(13,'MARKETING') --INSERTING A NEW RECORD IN SIMPLEVIEW
GO
SELECT * FROM SIMPLEVIEW -- NEW RECORD HAS BEEN ADDED
THROUGH VIEW, THIS RECORD WAS ADDED INTO BASE TABLE THAT YOU CAN FIND USING
BELOW QUERY
GO
SELECT * FROM DEP -- ANY
DML OPERATION PERFORMED ON VIEW MAKES IMPACT ON REFERENCED TABLES(BASE TABLES)
GO
DELETE FROM SIMPLEVIEW WHERE DEPID=13 -- DELETING NEWLY INSERTED
RECORD IN PREVIOUS STEP FROM SIMPLEVIEW
GO
SELECT * FROM SIMPLEVIEW
We have successfully inserted
and deleted records in simple view in our previous examples but there are
couple of point we need to make sure if you want to perform DML operation on
simple view.
1- Our view must have all NOT
NULL columns and primary key column, otherwise INSERT Command will fail.
2- The columns being modified
in the view must not be calculated column or must not
have aggregated function.
3- The columns being modified
can’t be affected by GROUP BY, HAVING or DISTINCT Clause.
Let’s go through with one
example to understand the concept
GO
TRUNCATE TABLE EMP --
TRUNCATING THE TABLE
GO
DELETE FROM DEP -- WE
CAN'T USE TRUNCATE COMMAND ON THIS TABLE BECAUSE IT IS BEING REFERENCED WITH
FOREIGN KEY IN EMP TABLE , SO USING DELETE COMMAND
GO
ALTER TABLE DEP ADD DEPFULLNAME VARCHAR(MAX) NOT NULL -- ADDING A NEW COLUMN WITH NOT NULL
CONSTRAINT
GO
/*INSERTING DATA INTO BOTH THE
TABLES*/
INSERT INTO DEP VALUES(10,'HR','HUMAN
RESOURCES'),(11,'SALES','SALES'),(12,'IT','INFORMATION TECHNOLOGY')
GO
INSERT INTO EMP VALUES(1,'GHANESH',10),(2,'PRASAD',11),(3,'GAUTAM',10),(4,'ANVIE',12),(5,'OJAL',12)
GO
As you know we have already
added a new column DEPFULLNAME which has NOT NULL constraint.
This column is not present in our previously created View (SimpleView). Let’s
try to insert a new record into SimpleView.
INSERT INTO DEP (DEPID) VALUES (13)
GO
The above query failed and gave
below error
Msg 515, Level 16, State 2, Line
1
Cannot insert the value NULL
into column 'DEPFULLNAME', table 'TEACHMESQLSERVER.dbo.DEP'; column does not
allow nulls. INSERT fails.
The statement has been
terminated.
Because behind the picture our
view was trying to insert this record in the base table DEP, our base table has
three columns DEPID which is primary key, DEPNAME (NULL is allowed) and
DEPFULLNAME (NULL is not allowed). Our query was trying to insert NULL records
for one of the NOT NULL column (DEPFULLNAME) that is why it gave error.
Let’s ALTER the DEPFULLNAME
column to accept NULL and try to run the same query again.
ALTER TABLE DEP ALTER COLUMN DEPFULLNAME VARCHAR(MAX)
GO
INSERT INTO DEP (DEPID) VALUES (13)
GO
SELECT * FROM SIMPLEVIEW
GO
SELECT * FROM DEP
We were able to successfully
insert a new record in the base table through view.
Complex View (Non Updatable View) explanation with example
Let’s create a complex view and
try to perform any DML operation
CREATE VIEW COMPLEXVIEW
AS
SELECT DEPNAME,EMPNAME FROM DEP INNER JOIN EMP ON DEP.DEPID= EMP.DEPID
GO
SELECT * FROM COMPLEXVIEW
GO
We have successfully created a
complex view and you can clearly see data is coming from two different tables
in complex view.
As I have already told Complex
Views are Non Updatable Views means you can’t perform any DML Operation on
complex view. If you don’t trust me you can try the below query to perform
insert operation.
INSERT INTO COMPLEXVIEW VALUES(13) -- Failed with error
GO
INSERT INTO COMPLEXVIEW VALUES('MARKETING','RAKESH') -- Failed with error
Error Message
Msg 4405, Level 16, State 1,
Line 1
View or function 'COMPLEXVIEW'
is not updatable because the modification affects multiple base tables.
Why complex views are
non-updatable it is because our complex view have columns from multiple tables
and when we try to insert new record through view It can’t understand which
column belongs to which table. We can make Complex view updatable by using Instead
of Trigger.
Our task is to distribute all
columns present in DML Command to their base tables.
Let’s make complex view updatable using Instead of Trigger
We are creating Instead of
Trigger (Click here to know about Triggers in SQL Server) on COMPLEXVIEW which takes data from Insert Command and insert into
their Base Tables.
GO
CREATE TRIGGER COMPLEWVIEWUPDATABLE ON COMPLEXVIEW
INSTEAD OF INSERT
AS
DECLARE @DEPNAME VARCHAR(MAX),@EMPNAME VARCHAR(MAX)
SELECT @DEPNAME=DEPNAME, @EMPNAME=EMPNAME FROM INSERTED
INSERT INTO DEP (DEPID,DEPNAME)VALUES((SELECT MAX(DEPID)+1 FROM DEP),@DEPNAME)
INSERT INTO EMP (EMPID,EMPNAME) VALUES((SELECT MAX(EMPID)+1 FROM EMP),@EMPNAME)
GO
INSERT INTO COMPLEXVIEW VALUES('MARKETING',’RAKESH’)
GO
This time our same Insert
Statement was successful, and it inserted MARKETING into DEP table and RAKESH
into EMP Table.
Let’s check
SELECT * FROM EMP
GO
SELECT * FROM DEP
New records were added
successfully
We can't use Delete Command on
complex view; you have to create Instead of Trigger if you want to perform
Delete Operation.
You can Use UPDATE Command on
complex view. Let’s take one example to explain this
GO
UPDATE COMPLEXVIEW SET DEPNAME='IT' WHERE DEPNAME='FINANCE'
GO
SELECT * FROM DEP
No comments:
Post a Comment