Views - the other face

To put it in a simple way : A view is a SELECT statement that has a name and is stored in Microsoft SQL Server.

Views act as virtual tables to provide several benefits.

a) Re-usability : A view gives developers a standardized way to execute queries, enabling them to write certain common queries once as views and then include the views in application code so that all applications use the same version of a query.

b) Security : A view provides a level of security by giving users access to just a subset of data contained in the base tables that the view is built over and can give users a more friendly, logical view of data in a database.

c) Performance : a view with indexes created on it can provide dramatic performance improvements, especially for certain types of complex queries.

We can categorize views as :
a) regular views, b) updateable views, c) indexed views.

A) Syntax of view creation
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH [ ,...n ] ]
AS select_statement [ ; ]
[ WITH CHECK OPTION ]
::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ] }

ENCRYPTION : Should View be encrypted
SCHEMABINDING : will not allow you to drop a table, view or function reference by this view without dropping the view
VIEW_METADATA : returns metadata about a view to client-side data access libraries.

Select statement can be of any complexity as long as it is a valid query with following exceptions

• should not have COMPUTE or COMPUTE BY clause
• should not have INTO keyword
• should not have OPTION clause
• should not reference a temporary table or table variable
• should not have ORDER BY clause unless it also specifies the TOP operator

Example of a view can be on AdventureWorks database
CREATE VIEW [vwEmployee]
AS
SELECT
e.[EmployeeID]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]


B) Modifying Data Through Views (UPDATEBLE VIEW)
Although views allows you to update data with some exception but it will be best practice if we use INSTEAD triggers.

Exception for data modification through VIEWS

• All changes must directly reference column, not derivations of a column
• View definition cannot contain GROUPBY or DISTINCT clause
• You cannot modify columns that are derived through aggregate functions (AVG,COUNT, SUM,MIN,SUBSTRING)
• You cannot reference columns generated by using operators such as union, crossjoin, and intersect.
• You cannot use TOP when you specify WITH CHECK OPTION

Example:
UPDATE [vwEmployee ]
SET [Phone] ='123-256-1685'
WHERE [employeeID] =1

C) INDEXED VIEW

An indexed view, also called a materialized view, causes SQL Server to execute the SELECT statement in the view definition. SQL Server then builds a clustered index on the view’s results, and stores the data and index within the database. As you change data in the base tables, SQL Server propagates these changes to the indexed view. If the result of the view could change from one execution to another or could change if different query options were set, the entire set of data SQL Server calculated and stored would be invalidated. Therefore, all the operators or functions that can cause varying results are disallowed.

Restrictions for index views

• View should be Schema bound
• The SELECT statement cannot reference other views.
• All functions must be deterministic. For example, you cannot use getdate() because every time it is executed, it returns a different date result.
• AVG, MIN, MAX, and STDEV are not allowed.

Example to create index on above view,
We will need to have it schema bound
ALTER VIEW [vwEmployee]
WITH
SCHEMABINDING
AS
SELECT
e.[EmployeeID]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]

Now we can create index as
CREATE UNIQUE CLUSTERED INDEX [IX_vwEmployee_EmpID] ON [vwEmployee]
(
[EmployeeID] ASC
)