VIEWS IN SQL
A view is a virtual table.
This chapter shows how to create, update, and delete a view.
SQL CREATE VIEW Statement
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view
are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and
present the data as if the data were coming from one single table.
A view is a virtual table based on a SELECT query. The query can
contain columns, computed columns, aliases, and aggregate functions
from one or more tables. The tables on which the view is based are called
base tables.
SQL Server stores data in tables, but you can create objects, called views,
that you query just like tables. Views do not store data; they are just
saved query definitions. Developers can use views to simplify coding.
Views can provide advantages over tables:
Views can represent a subset of the data contained in a table. A given
user may have permission to query the view, while denied access to
the rest of the base table.
Views can join and simplify multiple tables into a single virtual table.
Views can act as aggregated tables, where the database
engine aggregates data (sum, average, etc.) and presents the
calculated results as part of the data.
Views can hide the complexity of data.
Simplify Statements for User: Views allow users to select information
from multiple tables without actually knowing how to perform join.
Present Data in different perspective: Columns of views can be
renamed without affecting the tables on which the views are based.
1
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Note: A view always shows up-to-date data! The database engine recreates
the data, using the view's SQL statement, every time a user queries a view.
SQL CREATE VIEW Examples
If you have the Northwind database you can see that it has several views
installed by default.
The view "Current Product List" lists all active products (products that are not
discontinued) from the "Products" table. The view is created with the
following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
We can query the view above as follows:
SELECT * FROM [Current Product List]
Another view in the Northwind sample database selects every product in the
"Products" table with a unit price higher than the average unit price:
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
We can query the view above as follows:
SELECT * FROM [Products Above Average Price]
2
Another view in the Northwind database calculates the total sale for each
category in 1997. Note that this view selects its data from another view
called "Product Sales for 1997":
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName
We can query the view above as follows:
SELECT * FROM [Category Sales For 1997]
We can also add a condition to the query. Now we want to see the total sale
only for the category "Beverages":
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'
SQL Updating a View
You can update a view by using the following syntax:
SQL ALTER VIEW Syntax
ALTER VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Now we want to add the "Category" column to the "Current Product List"
view. We will update the view with the following SQL:
CREATE OR REPLACE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
3
SQL Dropping a View
You can delete a view with the DROP VIEW command.
SQL DROP VIEW Syntax
DROP VIEW view_name
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.
CREATE TABLE PRODUCTS
(ProductID INT PRIMARY KEY,
ProductDescVARCHAR(50) NOT NULL,
ManufacturingDate DATETIME,
ExpiryDate DATETIME,
IsSalable BIT,--1 Salable/Active FOR 0 ForNonSalable/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,
FNameVARCHAR(50) NOT NULL,
4
LNmeVARCHAR(50) NOT NULL,
UserIDVARCHAR(100) NOT NULL,
PswdNVARCHAR(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,
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'),
5
('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 table’s contents look like this:
SELECT * FROM Customer
CustIDFNameLNmeUserIDPswd
--------- -------- ---------- --------------- ---------
1002 Sara VermaS.Verma@abc.com S123
1004 Rick Singh G.Singh@xyz.com G311
1006 MickyKhera M.Khera@mno.com M222
(3 row(s) affected)
Select * from PRODUCTS
ProductIDProductDescManufacturingDateExpiryDateIsSalable Price
---------- ------------ ----------------------- ----------------------- --------- -
------
1 Biscuits 2011-09-01 00:00:00.000 2012-09-01 00:00:00.000
1 20.00
6
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
BookingIDProductIDCustIDDateOfBooking 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
7
,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;
Select * from Bill_V
FNameLNmeProductDescDateOfBooking 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
MickyKhera 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 if we want to view the customer information details. Now
exposing the Password might be risky, it’s strictly confidential info.
We create a View:
CREATE VIEW dbo.CustomerInfo_V
8
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...
Select * from CustomerInfo_V
Features
Views are 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.
Keeping these in mind, let’s turn to an example and perform
INSERTs/Updates/Deletes.
I am altering the below view as:
ALTER VIEW dbo.CustomerInfo_V
9
AS
Select CustID
,FNAME AS [FIRST NAME]
,LNME AS [LAST NAME]
,UserID
FROM dbo.Customer
Insert
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 butCustID is IDENTITY and PSWD has a DEFAULT. All the
other mandatory data was supplied in the insert query.
SELECT * FROM Customer
CustIDFNameLNmeUserIDPswd
--------- -------- ---------- --------------- ---------
1002 Sara VermaS.Verma@abc.com S123
1004 Rick Singh G.Singh@xyz.com G311
1006 MickyKhera M.Khera@mno.com M222
1008 GurumRamaswamy G.Ram@qrs.com password123
(4 row(s) affected)
Update
11
UPDATE CustomerInfo_V
SET [FIRST NAME]='Gurumoorthy'
WHERE [FIRST NAME]='Gurum'
SELECT * FROM Customer
CustIDFNameLNmeUserIDPswd
--------- -------- ---------- --------------- ---------
1002 Sara VermaS.Verma@abc.com S123
1004 Rick Singh G.Singh@xyz.com G311
1006 MickyKheraM.Khera@mno.com M222
1008 GurumoorthyRamaswamy G.Ram@qrs.com password123
(4 row(s) affected)
Delete
DELETE FROM CustomerInfo_V
WHERE [FIRST NAME]='Gurumoorthy'
SELECT * FROM Customer
CustIDFNameLNmeUserIDPswd
--------- -------- ---------- --------------- ---------
1002 Sara VermaS.Verma@abc.com S123
1004 Rick Singh G.Singh@xyz.com G311
11
1006 MickyKheraM.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:
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
12
,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.
13
