What is View in SQL Server?

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.