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 tables PRODUCTS, 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.

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

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:

Collapse | Copy Code

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.

Collapse | Copy Code

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.

Collapse | Copy Code

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

Collapse | Copy Code

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 Customer care executives to view the customer information details. Now exposing the Password might be risky, it’s strictly confidential info.
  We create a View which can be exposed to the API:
  
  Collapse | Copy Code

  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 fetch customer details –(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...
  
  Collapse | Copy 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:
  
  Collapse | Copy Code

  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 to SCHEMABIND our VIEWs.
  

  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. if CustomerInfo_V is schema bind no one will be able to alter the dbo.Customer table 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 table dbo.Customer without 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:
  
  Collapse | Copy Code

  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 a UNIQUE CLUSTERED INDEX to be able to create a NONCLUSTERED INDEX. By which I mean I can create the below index mandatorily post the creation of the Bill_View_Indx.
  Collapse | Copy Code

  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 NULL columns 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 the WHERE clause 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 unless INSTEAD OF Triggers are in place to handle the request.
  Keeping these in mind, let’s turn to an example and perform INSERTs/Updates/Deletes.
  I am altering the below view as:
  
  Collapse | Copy Code

  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

1. Insert

   Collapse | Copy Code

   INSERT INTO CustomerInfo_V
   ([FIRST NAME],[LAST NAME],UserID)
   VALUES ('Gurum','Ramaswamy','G.Ram@qrs.com')

   The insert happened because though the columns CustID and Pswd are mandatory but CustID is IDENTITY and PSWD has a DEFAULT. All the other mandatory data was supplied in the insert query.
   
   Collapse | Copy Code

   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)

2. Update

   Collapse | Copy Code

   UPDATE CustomerInfo_V
   SET [FIRST NAME]='Gurumoorthy'
   WHERE [FIRST NAME]='Gurum'
   SELECT * FROM Customer

   Collapse | Copy Code

   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)

3. Delete

   Collapse | Copy Code

   DELETE FROM CustomerInfo_V
   WHERE [FIRST NAME]='Gurumoorthy'
   SELECT * FROM Customer

   Collapse | Copy Code

   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' 

Alternatively what I do is in my SSMS.
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:

Collapse | Copy Code

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.

Collapse | Copy Code

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:

Collapse | Copy Code

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. 

HTML Javascript

A script is a small piece of program that can add interactivity to your website. For example, a script could generate a pop-up alert box message, or provide a dropdown menu. This script could be written using Javascript or VBScript.

You can write various small functions, called event handlers using any of the scripting language and then you can trigger those functions using HTML attributes.

Now a days only Javascript and associated frameworks are being used by most of the web developers, VBScript is not even supported by various major browsers.

You can keep Javascript code in a separate file and then include it whereever it's needed, or you can define functionality inside HTML document itself. Let's see both the cases one by one with suitable examples.

External Javascript

If you are going to define a functionality which will be used in various HTML documents then it's better to keep that functionality in a separate Javascript file and then include that file in your HTML documents. A Javascript file will have extension as .js and it will be included in HTML files using <script> tag.

Example

Consider we define a small function using Javascript in script.js which has following code:

function Hello()
{
    alert("Hello, World");
}

Now let's make use of the above external Javascript file in our following HTML document:

<!DOCTYPE html>
<html>
<head>
<title>Javascript External Script</title>
<script src="/html/script.js" type="text/javascript"/></script>
</head>
<body>
<input type="button" onclick="Hello();" name="ok" value="Click Me"  />
</body>
</html>

This will produce following result, where you can try to click on the given button:

Internal Script

You can write your script code directly into your HTML document. Usually we keep script code in header of the document using <script> tag, otherwise there is no restriction and you can put your source code anywhere in the document but inside <script> tag.

Example

<!DOCTYPE html>
<html>
<head>
<title>Javascript Internal Script</title>
<base href="http://www.tutorialspoint.com/" />
<script type="text/javascript">
function Hello(){
   alert("Hello, World");
}
</script>
</head>
<body>
<input type="button" onclick="Hello();" name="ok" value="Click Me"  />
</body>
</html>

This will produce following result, where you can try to click on the given button:

Event Handlers

Event handlers are nothing but simply defined functions which can be called against any mouse or keyboard event. You can define your business logic inside your event handler which can vary from a single to 1000s of line code.

Following example explains how to write an event handler. Let's write one simple functionEventHandler() in the header of the document. We will call this function when any user brings mouse over a paragraph.

<!DOCTYPE html>
<html>
<head>
<title>Event Handlers Example</title>
<base href="http://www.tutorialspoint.com/" />
<script type="text/javascript">
function EventHandler(){
   alert("I'm event handler!!");
}
</script>
</head>
<body>
<p onmouseover="EventHandler();">Bring your mouse here to see an alert</p>
</body>
</html>

Now this will produce following result. Bring your mouse over this line and see the result:

Bring your mouse here to see an alert

Hide Scripts from Older Browsers

Although most (if not all) browsers these days support Javascript, but still some older browsers don't. If a browser doesn't support JavaScript, instead of running your script, it would display the code to the user. To prevent this, you can simply place HTML comments around the script as shown below.

JavaScript Example:
<script type="text/javascript">
<!--
document.write("Hello Javascript!");
//-->
</script>

VBScript Example:
<script type="text/vbscript">
<!--
document.write("Hello VBScript!")
'-->