SQL Views

A SQL View is a virtual table, which is based on SQL SELECT query. Essentially a view is very close to a real database table (it has columns and rows just like a regular table), except for the fact that the real tables store data, while the views don’t. The view’s data is generated dynamically when the view is referenced. A view references one or more existing database tables or other views.

Advantages of Views

1. Views provide a security mechanism by sub setting the data by rows (All Active Customers, all customers in a certain state) and by columns (Payroll fields not shown in the Employee Phone List View).
2. Views can simplify complex queries into a single reference. Complex Join operations that can make a normalized database design of several tables into a single row in the result set of the view. This is great for reporting tools like Crystal and Cognos.
3. Views give us aggregation capabilities (Min, Max, Count, Sum) where the data is not stored but calculated.
4. Views can create other calculated fields based on values in the real underlying tables.
5. Views can hide the complexity of partitioned data (Sales from 1998 are in the 1998 table, Sales from 1999 are in the 1999 table, Sales from 2000 are in the Current Table).
6. Views can be updateable in certain situations
7. Views do not incur overhead of additional permanent storage.
8. Data Access from view is independent of underlying database design.

Creating a View

Create View EMP_VIEW WITH ENCRYPTION | SCHEMABINDING SELECT Empname,PkEmpid ,Salary*12 as Annual_Salary from Employee WITH CHECK OPTION

Note: Views cannot include ORDER BY clause and Cannot include INTO keyword

WITH ENCRYPTION prevents the users to see the statement on which view is based.

WITH SCHEMABINDING clause is used then the base table cannot be dropped or modified in a way that would affect the view

WITH CHECK OPTION clause is then row cannot be updated through a view if it would no longer be included in the view.

Modifying Data Through Views (Updatable Views)

Cannot modify views with more than one base tables.

The select list can’t include a DISTINCT or TOP clause or an aggregate function or a calculated value.

The select statement can’t include a GROUP BY or HAVING clause or UNION operator.

Can cause errors if they affect columns that are not referenced in the View

If the WITH CHECK OPTION has been specified, makes sure that inserted or updated row meets the select condition.

WITH ENCRYPTION OPTION Create View EmployeeView With Encryption as Select * From Employee Where FkDeptId=10 sp_helptext ‘EmployeeView’ This procedure will not give the statement on which view is based as it is encrypted

WITHOUT CHECK OPTION Insert Into EmployeeView (PkEmpId, EmpName, Job, Manager, Hiredate, Salary, FkDeptId) Values (6569, ‘Robert', 'CLERK', 7902, '12/17/1980', 800, 20)—successful

WITH CHECK OPTION Alter View EmployeeView As Select * From Employee Where FkDeptId=10 With Check Option Insert Into EmployeeView (PkEmpId, EmpName, Job, Manager, Hiredate, Salary, FkDeptId) Values (6570, 'SHITAL', 'CLERK', 7902, '12/17/1980', 800, 20)--fails

SQL Server Catalog Views

Catalog views return information that is used by the SQL Server Database Engine sys.tables, sys.views, sys.columns, sys.key_constraints, sys.foreign_keys, sys.foreign_key_columns, sys.objects.

About Author:
Rachna Bhagwe works with Systems Plus Pvt. Ltd. and is working on .net technologies. She can be contacted at rachna.b@spluspl.com