Elaborate Look at VIEW in SQL SERVER 2008

What is VIEW?

View is a database object. It is a virtual table because result set which are returned by view has general form of table like rows and columns. It contains the subset of columns from one or more database tables. It is a parsed SQL Statement which fetches records at the time of execution.

Advantages of View

1. View is used for security purpose which shows only the defined columns to the end-user and   restricts the confidential fields.
2. Data abstraction – End user will not aware of the all the data present in the source tables.
3. It reduces the complexities of writing complex queries.
4.  It improves performance by using Materialized Views. [If frequency of selection is more than the frequency of updating].

Syntax

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH <view_attribute> [ ,...n ] ]

AS select_statement

[WITH CHECK OPTION] [ ; ]

<view_attribute> ::=

{

    [ENCRYPTION]

    [SCHEMABINDING]

    [VIEW_METADATA]     }

Using WITH CHECK OPTION

It forces all data modifications against the view to follow the criteria set that are mentioned in the SELECT Clause of View definition.

But it does not affect in the source tables, even if CHECK OPTON specified in the relevant VIEW definition.

Let’s walk through example.

Create Table Employee

(     Id INT IDENTITY(1,1),

Name Varchar(100),

salary INT,

ReportsTo Varchar(100)

)

Insert into Employee values ( 'Joseph', 7000, NULL)

Insert into Employee values ( 'Mary', 3000, 'Joseph')

Insert into Employee values ( 'Rakshan', 2000, 'Joseph')

Insert into Employee values ( 'Jack', 6000, 'Joseph')

Insert into Employee values ( 'Akilan', 1000, 'Jack')

Insert into Employee values ( 'Mark', 3500, 'Jack')

Insert into Employee values ( 'Premji', 5000, 'Jack')

Insert into Employee values ( 'Carolin', 2500, 'Jack')

Create View Emp_Report

AS

SELECT Id, Name,ReportsTo FROM Employee WHERE ReportsTo = 'Jack'

WITH CHECK OPTION;

UPDATE Emp_Report

SET ReportsTo = 'Joseph'

WHERE Name = 'Premji'

Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

UPDATE Employee

SET ReportsTo = 'Joseph'

WHERE Name = 'Premji'

(1 row(s) affected)

Using ENCRYPTION

It encrypts the view definition at SYS.SYSCOMMENTS [‘text’ column] table. Also it prevents to publish the view as part of the SQL Server Replication.

Create View Emp_Report

WITH ENCRYPTION

AS

SELECT Id, Name,ReportsTo FROM Employee WHERE ReportsTo = 'Jack'

>> SELECT text FROM SYS.syscomments

NULL

>> sp_helptext Emp_Report

The text for object 'Emp_Report' is encrypted.

Using SCHMEABINDING

It binds the view to the schema of the refererred source tables. Source table or tables can not be modified such a way that it affects the view definition. While specifying SCHEMABINDING option, we should use two part table names [ schema.object ] in the SELECT clause. Also referred tables must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding.  Also while executing, ALTER TABLE statements on source tables that participate in views that have schema binding fail when these statements affect the view definition.

Create View Emp_Report

WITH SCHEMABINDING

AS

SELECT Id, Name,ReportsTo FROM dbo.Employee WHERE ReportsTo = 'Jack'

>> DROP TABLE Employee

Msg 3729, Level 16, State 1, Line 1

Cannot DROP TABLE 'Employee' because it is being referenced by object 'Emp_Report'.

>> ALTER TABLE EMPLOYEE ALTER COLUMN NAME VARCHAR(200)

Msg 5074, Level 16, State 1, Line 1

The object 'Emp_Report' is dependent on column 'NAME'.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE ALTER COLUMN NAME failed because one or more objects access this column.

Using VIEW_METADATA

It returns the Meta data information about view to the DB-Library, ODBC and OLEDB APIs instead of referring it from source tables. While returning Meta data information of views, it shows only the view name and not the underlying base table names.

Suppose third party application is referring to a database view and it needs to know the Meta data information of the columns that are referenced in the views. If View is created using WITH VIEW_METADATA option, then database returns the Meta data information of the views as well as data to the application request.

Create View Emp_Report

WITH VIEW_METADATA

AS

SELECT Id, Name,ReportsTo FROM dbo.Employee WHERE ReportsTo = 'Jack'

Types of Views

We can catagorize views into 3 different types.

1. Standard View
2. Indexed View
3. Partitioned View

     1. Standard View

It is a normal standard view definition which most of the developers use. The result set of standard view is not permanently stored in the database. Each time while we querying standard view, internally database engine substitutes the definition of the view into the query.

Create View Emp_Report

AS

SELECT Id, Name,ReportsTo FROM Employee WHERE ReportsTo = 'Jack'

DML Operations can be applicable on views if we are referring single table on view definition.

     2. Indexed View

Indexed view is nothing but creating clustered index on the columns defined in the views. It is more useful when we have complex queries, multiple aggregate operations, and multiple joins, join and aggregates that process many rows. It works best when underlying data is infrequently updated.

If underlying data is frequently updated, then cost of maintaining indexed view will be outweighing which may not give any performance efficiency.

If underlying data is updated periodically or in batches and primarily it is treated as read-only in between updates, then for each periodical update drop and rebuild the indexed view for better performance of the indexed view. 

Create Table Employee

(    

Id INT IDENTITY(1,1) CONSTRAINT Cons_Prim_Employee PRIMARY KEY,

Name Varchar(100),

salary INT,

ReportsTo Varchar(100)

)

Insert into Employee values ( 'Joseph', 7000, NULL)

Insert into Employee values ( 'Mary', 3000, 'Joseph')

Insert into Employee values ( 'Rakshan', 2000, 'Joseph')

Insert into Employee values ( 'Jack', 6000, 'Joseph')

Insert into Employee values ( 'Akilan', 1000, 'Jack')

Insert into Employee values ( 'Mark', 3500, 'Jack')

Insert into Employee values ( 'Premji', 5000, 'Jack')

Insert into Employee values ( 'Carolin', 2500, 'Jack')

Create Table Dept

(

 Dept_Id INT,

 Emp_Id INT CONSTRAINT Cons_Forg_Dept FOREIGN KEY REFERENCES Employee(Id),

 Dept_Name VARCHAR(100)

 )

 Insert into Dept Values (101, 1, 'RETAIL')

 Insert into Dept Values (101, 2, 'RETAIL')

 Insert into Dept Values (101, 3, 'RETAIL')

 Insert into Dept Values (101, 4, 'RETAIL')

 Insert into Dept Values (102, 5, 'FINANCE')

 Insert into Dept Values (102, 6, 'FINANCE')

 Insert into Dept Values (102, 7, 'FINANCE')

 Insert into Dept Values (102, 8, 'FINANCE')

 CREATE VIEW Emp_Sal_Report

 WITH SCHEMABINDING

 AS

 SELECT    

            D.Dept_Name As DEPT_NAME,

            SUM(ISNULL(E.salary,0)) AS AVG_DEPT_SALARy,

            COUNT_BIG(*) AS DEP_COUNT

 FROM

            dbo.Employee E

            INNER JOIN dbo.Dept D ON E.Id = D.Emp_Id

 GROUP BY

            D.Dept_Name

 CREATE UNIQUE CLUSTERED INDEX Idx_View ON Emp_Sal_Report(DEPT_NAME)

 SElECT * FROM Emp_Sal_Report Order by DEPT_NAME

     3.  Partitioned Views

It Joins one or more member tables from one or more SQL Servers that making data appear as if from one Table.

If it joins one or more member tables from single SQ Server instance, then it is called Local Partitioned Views.

If it joins one or more member tables from multiple SQL Server instances, then it is called Distributed Partitioned Views.

Lets walk through example for Local Partitioned Views..

Partitioned views allow the data in a large table to be split into smaller member tables. The data is partitioned between the member tables based on ranges of data values in ‘Emp_Category’ column. The data ranges for each member table are defined in a CHECK constraint specified on the partitioning column.

Emp_Category column has 4 different categories ‘BEGINNER’, ‘SE’, ‘SSE’ and ‘ARCHITECT’

CREATE TABLE Employee_Fresher_List

(

Emp_Id INT PRIMARY KEY,

Name Varchar(100),

salary INT,

Emp_Category Varchar(100)

 CHECK (Emp_Category = 'BEGINNER') 

)    

GO

A view that uses UNION ALL to combine selects of all the member tables into a single result set is then defined. When SELECT statements referencing the view specify a search condition on the partition column, the query optimizer uses the CHECK constraint definitions to determine which member table contains the rows.

CREATE VIEW Company_EMP_LIST

AS

SELECT * FROM Employee_Fresher_List

UNION ALL

SELECT * FROM Employee_SE_List

UNION ALL

SELECT * FROM Employee_SSE_List

UNION ALL

SELECT * FROM Employee_ARCHITECT_List

SELECT *

FROM Company_EMP_LIST

WHERE Emp_Category IN ('SE','SSE')

Points to be noted on VIEW

  

1)   A View can have maximum of 1024 columns.

2)   Sp_refreshview [If view is not created using WITH SCHEMABINDING option] should be run to refresh the view definition, when changes are made to the objects underlying the view that affects the definition of the view. 

3)   When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies. The text of the CREATE VIEW statement is stored in the sys.sql_modules  and sys.syscomments catalog view.

 4)   The Database Engine saves the settings of SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a view is created. These original settings are used to parse the view when the view is used. Therefore, any client-session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS do not affect the view definition when the view is accessed .

5)   Select clause in the view definition cannot include below list

a.    Order By clause unless there is also a TOP Clause.

b.    INTO Keyword.

c.    OPTION Clause

d.    Reference to a temporary table or Table variable.