A view can be thought of as
either a virtual table or a stored query. The data accessible through a view is
not stored in the database as a distinct object. Only SELECT statement is stored in the database. The result set of the SELECT statement forms the virtual
table returned by the view.
Views are called virtual tables because the result
set of a view is us not usually saved in the database. The result set for a view
is dynamically incorporated into the logic of the statement and the result set
is built dynamically at run time.
Views are created by defining the SELECT statement that retrieves the
data to be presented by the view. The data tables referenced by the SELECT
statement are known as the base tables for the view.
Views can be created to hide data complexity as well data security.
For instance you have three tables, Employee, Department, and BasicPay and you
want to display only specific columns from these table.
Table / Entity
|
Fields / Attributes
|
Comments
|
Employee
|
(EmployeeID, EmployeeName, DOB, DepartmentID)
|
User can see all the attributes.
|
Department
|
(DepartmentID, DepartmentName, ManagerEmployeeID)
|
User can see all the attributes.
|
BasicPay
|
(EmployeeID, StartDate, EndDate, PayScale, BasicAmt,
BonusAmt, SalaryCurreny)
|
User can see only PayScale and should not have any access to
BasicAmt and BonusAmout.
|
Suppose user is interested in following reporting data:
SELECT e.EmployeeName
,d.DepartmentName
,mgr.FirstName AS DeptManagerName
,b.PayScale
FROM dbo.Employee e
LEFT JOIN Department d
ON d.DepartmentID = e.DepartmentID
LEFT JOIN dbo.Employee mgr
ON mgr.EmployeeID = d.ManagerEmployeeID
LEFT JOIN dbo.BasicPay b
ON b.EmployeeID = e.EmployeeID
Now you can create view a view to store the above query and grant
access to the view instead of granting access to all three tables.
IF OBJECT_ID('dbo.vwEmployeeDetails') IS NOT NULL
BEGIN
PRINT 'Dropping view
dbo.vwEmployeeDetails'
DROP VIEW dbo.vwEmployeeDetails
END
GO
PRINT 'Creating view
dbo.vwEmployeeDetails'
GO
CREATE VIEW dbo.vwEmployeeDetails
AS
/***********************************************
Created By :
Author Name
Created Date :
MM/DD/YYYY
History
-----------------------------------------------
MM/DD/YYYY Comments
***********************************************/
SELECT e.EmployeeName
,d.DepartmentName
,mgr.FirstName AS DeptManagerName
,b.PayScale
FROM dbo.Employee e
LEFT JOIN Department d
ON d.DepartmentID = e.DepartmentID
LEFT JOIN dbo.Employee mgr
ON mgr.EmployeeID = d.ManagerEmployeeID
LEFT JOIN dbo.BasicPay b
ON b.EmployeeID = e.EmployeeID
GO
Now you can pull the data directly from the view instead of using
complex query:
SELECT EmployeeName
,DepartmentName
,DeptManagerName
,PayScale
FROM dbo.vwEmployeeDetails
Advantages
of View
There are several advantages of views as mentioned below:
·
We can hide
data complexity in a view. Instead of forcing users to learn the T-SQL syntax,
you might wish to provide a view that runs a commonly requested SQL statement.
·
View is an
ideal object to protect your data. If you have a table containing sensitive
data in certain columns, you might wish to hide those columns from certain
groups of users. For instance allow employees who do not work in payroll to see
the name, work phone, and department columns in an employee table, but do not
allow them to see any columns with salary information or personal information,
so you can create a view that only displays required columns. You can grant
permissions to that view without allowing users to query the underlying tables.
·
Join
columns from multiple tables so that they look like a single table.
·
Enforcing
some simple business rules. For example, if you wish to generate a list of
customers that need to receive the fall catalog, you can create a view of
customers that have previously bought your shirts during the fall.
·
Aggregate
information instead of supplying details. For example, present the sum of a
column, or the maximum or minimum value from a column.
·
Data
exports with BCP. If you are using BCP to export your SQL Server data into text
files, you can format the data through views since BCP's formatting ability is
quite limited.
·
Customizing
data. If you wish to display some computed values or column names formatted
differently than the base table columns, you can do so by creating views.
Disadvantages
of View
·
Even though
views can be a great tool for securing and customizing data, they can be performance
bottleneck. Indeed, they are not any faster than the query that defines them.
Note: From SQL
Server 2000 onwards, indexed views (also referred to as "materialized"
views) are supported to overcome this limitation.
·
Views can
especially degrade the performance if they are based on other views. Therefore,
it is recommended NOT to create views based on other views.
