Thursday, July 31, 2014

Types of Views in SQL Server

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
2-                  Indexed Views
3-                  Partitioned 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: