Views In SQL Server
Introduction
This article discusses one of the basic but greatly misunderstood objects in SQL server – “Views”. Views could be looked as an additional layer on the table which enables us to protect intricate or sensitive data based upon our needs. It’s like a window exposed to the flocks and they can see very selective items inside the room through the window. Since views are an additional layer sure they do add an overhead but there is a tradeoff for situations when they are of great help.As we proceed, we will try to learn more about them not by theoretical explanation but by some practical examples.
Uses
We begin with creating 3 tablesPRODUCTS, Customer & BOOKING. These are fictitious tables for our demo. The PRODUCTS stores data for a retail shop with a flag column IsSalable based on whose value we treat the products as Salable. 
CREATE TABLE PRODUCTS
(ProductID INT PRIMARY KEY CLUSTERED,
ProductDesc VARCHAR(50) NOT NULL,
ManufacturingDate DATETIME,
ExpiryDate DATETIME,
IsSalable BIT,--1 Salable/Active FOR 0 For NonSalable/Passive Product
Price MONEY NOT NULL
)
Next, we have a Customer table which stores UserID and Password details for customers. CREATE TABLE Customer
(CustID INT IDENTITY(1002,2)PRIMARY KEY CLUSTERED,
FName VARCHAR(50) NOT NULL,
LNme VARCHAR(50) NOT NULL,
UserID VARCHAR(100) NOT NULL,
Pswd NVARCHAR(100) NOT NULL DEFAULT 'password123'
)
Lastly, I have created a BOOKING table which houses all the bookings from different customers. CREATE TABLE BOOKING
( BookingID INT IDENTITY(10,2) PRIMARY KEY CLUSTERED,
ProductID INT REFERENCES dbo.Products(ProductID),
CustID INT REFERENCES dbo.Customer(CustID),
DateOfBooking DATETIME NOT NULL,
QTY INT
)
Next, insert a few records into these tables: INSERT INTO PRODUCTS VALUES
(1,'Biscuits','2011-09-01 00:00:00.000','2012-09-01 00:00:00.000',1,20),
(2,'Butter','2010-09-01 00:00:00.000','2011-09-01 00:00:00.000',1,30),
(3,'Milk','2011-10-01 00:00:00.000','2011-11-01 00:00:00.000',1,46)
INSERT INTO Customer (FName,LNme,UserID,Pswd)
VALUES
('Sara','Verma','S.Verma@abc.com','S123'),
('Rick','Singh','G.Singh@xyz.com','G311'),
('Micky','Khera','M.Khera@mno.com','M222')
INSERT INTO BOOKING (ProductID,CustID,DateOfBooking,QTY)
VALUES
(1,1002,'2011-11-01 00:00:00.000',3),
(2,1004,GETDATE(),4),
(3,1006,'2011-10-01 00:00:00.000',2)
Our tables contents look like this. I know the tables are not
completely normalized, for now please ignore them, these are simple demo
tables. SELECT * FROM Customer
CustID FName LNme UserID Pswd
--------- -------- ---------- --------------- ---------
1002 Sara Verma S.Verma@abc.com S123
1004 Rick Singh G.Singh@xyz.com G311
1006 Micky Khera M.Khera@mno.com M222
(3 row(s) affected)
Select * from PRODUCTS
ProductID ProductDesc ManufacturingDate ExpiryDate IsSalable Price
---------- ------------ ----------------------- ----------------------- --------- -------
1 Biscuits 2011-09-01 00:00:00.000 2012-09-01 00:00:00.000 1 20.00
2 Butter 2010-09-01 00:00:00.000 2011-09-01 00:00:00.000 1 30.00
3 Milk 2011-10-01 00:00:00.000 2011-11-01 00:00:00.000 1 46.00
(3 row(s) affected)
Select * from BOOKING
BookingID ProductID CustID DateOfBooking QTY
----------- ----------- ----------- ----------------------- -----------
10 1 1002 2011-11-01 00:00:00.000 3
12 2 1004 2011-10-09 17:31:31.790 4
14 3 1006 2011-10-01 00:00:00.000 2
(3 row(s) affected)
A customer purchases/books a product and the same gets recorded into the BOOKING table
now to generate the bill on his name we can uses a VIEW which would
help us do away with a physical table. Instead it would enable us to
generate the bill based on the information from these 3 tables itself.
Let’s see how it’s possible. CREATE VIEW Bill_V
AS
SELECT C.FName
,C.LNme
,P.ProductDesc
,B.DateOfBooking
,P.Price
,B.QTY
,(B.QTY*P.Price) AS TotalAmountPayable
FROM BOOKING B
INNER JOIN PRODUCTS P
ON B.ProductID=P.ProductID
INNER JOIN Customer C
ON B.CustID=C.CustID;
Next if I,
Select * from Bill_V
FName LNme ProductDesc DateOfBooking Price QTY TotalAmountPayable
-------------------------------------------------- ------------------------------------
Sara Verma Biscuits 2011-11-01 00:00:00.000 20.00 3 60.00
Rick Singh Butter 2011-10-09 17:31:31.790 30.00 4 120.00
Micky Khera Milk 2011-10-01 00:00:00.000 46.00 2 92.00
(3 row(s) affected)
We have been able to generate the bill based on the 3 tables hence we
have not only optimized the bill generation also we have saved
ourselves from hosting a physical table in the database with this
information. - This is the most credible use of a VIEW; it can not only reduce
apparent complexity but also prevent redundant hosting of data in the
DB.
Next say there is some API which enables the
Customercare executives to view thecustomerinformation details. Now exposing the Password might be risky, it’s strictly confidential info.
We create a View which can be exposed to the API:
CREATE VIEW dbo.CustomerInfo_V AS Select CustID ,FNAME AS [FIRST NAME] ,LNME AS [LAST NAME] ,UserID FROM dbo.Customer
We have a created a View which can be used by the API to fetchcustomerdetails –(Minus) the Password Column.
- Views can be used to prevent sensitive information from being selected, while still allowing other important data.
Views do not have a physical existence, but still they do return a
set of record set as a table does, the differences is it is simply an
additional layer which calls the underlying code which finally returns
the record set.
When I execute the code...
Select * from CustomerInfo_V
...I get recordsets as I would get in a table with the only difference that the data returned is as per the below query:
Select CustID ,FNAME AS [FIRST NAME] ,LNME AS [LAST NAME] ,UserID FROM dbo.Customer
But arguably, we still get a set of records, isn’t it? So say if there are 1 million customers in my database, wouldn’t it be cool if I have clustered/non clustered index on my view for optimized queries. But is it possible as the view doesn’t host data physically, the answer is yes. It is possible to have indexes on views. But before we find ourselves capable of creating index on views, we have toSCHEMABINDourVIEWs.
What is SCHEMABINDING a VIEW
Schema binding binds your views to the dependent physical columns of the accessed tables specified in the contents of the view, i.e. ifCustomerInfo_Vis schema bind no one will be able to alter thedbo.Customertable unless they drop the table.
Why would we need that?
The answer is, it prevents your views from being orphaned. Just think that someone drops/alters the tabledbo.Customerwithout paying any heed to our view. Now that would leave our view nowhere. Hence schema bind it, this will prevent any such accidents from happening.
Also to be able to create an index on the view you need it essentially schema bound.
Let’s make the change:
ALTER VIEW Bill_V WITH SCHEMABINDING AS SELECT C.FName ,C.LNme ,P.ProductDesc ,B.DateOfBooking ,P.Price ,B.QTY ,(B.QTY*P.Price) AS TotalAmountPayable FROM dbo.BOOKING B INNER JOIN dbo.PRODUCTS P ON B.ProductID=P.ProductID INNER JOIN dbo.Customer C ON B.CustID=C.CustID; Now we are licensed to have an Index on this dbo.Bill_V view. CREATE UNIQUE CLUSTERED INDEX Bill_View_Indx ON dbo.Bill_V(Fname,LNme);
Cool now we have an index on the view, remember you need to have aUNIQUE CLUSTERED INDEXto be able to create aNONCLUSTERED INDEX. By which I mean I can create the below index mandatorily post the creation of theBill_View_Indx.CREATE NONCLUSTERED INDEX Bill_View_Indx2 ON dbo.Bill_V(ProductDesc);
- So next use of the View is to be able to create an additional index upon the db to speed up your query performance.
Features
Are views only meant for reading data in a customized mode? Not really views also facilitate DML (Insert/Update/Delete). But there is a set of rules which needs to be adhered to enable DMLs.
- If you are using a view to insert data, then your view should have a
single select and also all the mandatory columns of the “being edited”
table must be included in the view unless the table has a default values
for all
NOT NULLcolumns of the table. - Secondly don’t forget, for views with “
WITH CHECK” options enabled, it’s important to keep in mind that the data begin inserted qualifies in theWHEREclause of the view and is certain to be selected by the view. Simply put the data you insert is picked up while you select from your view. - If the view is having
joins with more than one table, then most cases chances of modifying capabilities are negligible unlessINSTEAD OFTriggers are in place to handle the request.
INSERTs/Updates/Deletes.
I am altering the below view as:
ALTER VIEW dbo.CustomerInfo_V WITH SCHEMABINDING AS Select CustID ,FNAME AS [FIRST NAME] ,LNME AS [LAST NAME] ,UserID FROM dbo.Customer WITH CHECK OPTION
- If you are using a view to insert data, then your view should have a
single select and also all the mandatory columns of the “being edited”
table must be included in the view unless the table has a default values
for all
-
Insert
INSERT INTO CustomerInfo_V ([FIRST NAME],[LAST NAME],UserID) VALUES ('Gurum','Ramaswamy','G.Ram@qrs.com')
Theinserthappened because though the columnsCustIDandPswdare mandatory butCustIDisIDENTITYandPSWDhas aDEFAULT. All the other mandatory data was supplied in theinsertquery.
SELECT * FROM Customer CustID FName LNme UserID Pswd --------- -------- ---------- --------------- --------- 1002 Sara Verma S.Verma@abc.com S123 1004 Rick Singh G.Singh@xyz.com G311 1006 Micky Khera M.Khera@mno.com M222 1008 Gurum Ramaswamy G.Ram@qrs.com password123 (4 row(s) affected)
-
Update
UPDATE CustomerInfo_V SET [FIRST NAME]='Gurumoorthy' WHERE [FIRST NAME]='Gurum' SELECT * FROM Customer
CustID FName LNme UserID Pswd --------- -------- ---------- --------------- --------- 1002 Sara Verma S.Verma@abc.com S123 1004 Rick Singh G.Singh@xyz.com G311 1006 Micky Khera M.Khera@mno.com M222 1008 Gurumoorthy Ramaswamy G.Ram@qrs.com password123 (4 row(s) affected)
-
Delete
DELETE FROM CustomerInfo_V WHERE [FIRST NAME]='Gurumoorthy' SELECT * FROM Customer
CustID FName LNme UserID Pswd --------- -------- ---------- --------------- --------- 1002 Sara Verma S.Verma@abc.com S123 1004 Rick Singh G.Singh@xyz.com G311 1006 Micky Khera M.Khera@mno.com M222 Displaying the View Contents For retrieving what is under the hood of the view use, EXECUTE SP_HELPTEXT 'dbo.CustomerInfo_V'
Tools > Options > Environment >Keyboard > Ctrl-F1 ==
SP_HELPTEXT From next time, to see the contents of a
VIEW/StoreProcedure, simply select it and hit Ctrl+F1? Refreshing Views
Just in case we are working with a non-schema bound view and there is some change in the underlying table, to prevent the view from producing unexpected results, we have an option to refresh the view with:EXECUTE SP_REFRESHVIEW 'dbo.BILL_V'
This updates the metadata of a non-schema bound view. Encrypting your Views
The “WITH ENCRYPTION” option encrypts the views by which I mean it will not be visible via SP_HELPTEXT so
in case of strict requirements where the contents of the view don’t
need to be exposed this option freezes the view. It’s important to save
the contents script in some archive to be able to retrieve the code for
any change. ALTER VIEW Bill_V
WITH ENCRYPTION
AS
SELECT C.FName
,C.LNme
,P.ProductDesc
,B.DateOfBooking
,P.Price
,B.QTY
,(B.QTY*P.Price) AS TotalAmountPayable
FROM dbo.BOOKING B
INNER JOIN dbo.PRODUCTS P
ON B.ProductID=P.ProductID
INNER JOIN dbo.Customer C
ON B.CustID=C.CustID;
So we have encrypted the view, now if we try to EXECUTE SP_HELPTEXT 'dbo.Bill_V'. The result would be:
The text for object ' Bill_V ' is encrypted.
It is not advised to encrypt your views unless you have a very strong
reason behind it. That was some key elements involved. I hope the next
time you work with views, this article will help you make better
decisions. 
No comments:
Post a Comment
thanks......