SQL SERVER – VIEWS

What is a View In SQL SERVER

• A view can be thought of as either a virtual table or a stored query, like a real table, a view consists of a set of named columns and rows of data.

• Unless a view is indexed, its data is not stored in the database as a distinct object.
• What is stored in the database is a SELECT statement.
• The result set of the SELECT statement forms the virtual table returned by the view.
• A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced.
• The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.
• A view acts as a filter on the underlying tables referenced in the view.
• The query that defines the view can be from one or more tables or from other views in the current or other databases.
• There are no restrictions on querying through views and few restrictions on modifying data through them.

Syntax: CREATE VIEW <view_name> [(column [,...n])]

[WITH <view_attribute> [,...n]]

AS select_statement

[WITH CHECK OPTION]

Under the view_attribute we have the following options:

[ENCRYPTION]

[SCHEMABINDING]

Types of Views:

• Simple Views
• Complex Views

Simple Views:

-          These Views as based upon a single table, which access the data from the single table.

-          They contain a Sub Query which retrieves the data from one base table.

CREATE VIEW SIMPLE_VIEW

AS SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP

-Once the view is created we can access the data from it as if it was a table as following:

SELECT * FROM SIMPLE_VIEW

SELECT EMPNO, ENAME, SAL, SAL*12 AS [ANNUAL SAL], DEPTNO FROM SIMPLE_VIEW

SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO

-We can also perform DML operations on the Simple Views which will effect on the base table.

INSERT INTO SIMPLE_VIEW VALUES(1234, 'SANTOSH', 4300, 20)

DELETE FROM SIMPLE_VIEW WHERE DEPTNO=20

UPDATE EMP SET SAL=5600 WHERE EMPNO=1001

-All the columns that are referenced in the view can be modified through the view.

-We cannot perform insert operations on the view if he view does not contain all the not null columns of the base table.

Complex Views:

-          If the View is based on multiple tables it is a complex view

-          If it is based on a single table with any of the following:

• Group By Clause
• Having Clause
• Group Functions
• Distinct Function
• Function Calls

CREATE VIEW EMP_DEPT

AS

SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC

FROM EMP E INNER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

CREATE VIEW EMP_GRADE

AS

SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE, S.LOSAL, S.HISAL

FROM EMP E INNER JOIN SALGRADE S

ON E.SAL BETWEEN S.LOSAL AND S.HISAL

CREATE VIEW EMP_MANAGERS

AS

SELECT E.ENAME + ' WORKS UNDER ' + M.ENAME

FROM EMP E INNER JOIN EMP M

ON E.MGR=M.EMPNO

CREATE VIEW EMP_SALARIES

AS

SELECT EMPNO, ENAME, DEPTNO, SAL AS MONTHLY, SAL*12 AS ANNUAL FROM EMP

CREATE VIEW EMP_DESIGNATIONS

AS

SELECT JOB FROM EMP WHERE DEPTNO=10

UNION

SELECT JOB FROM EMP WHERE DEPTNO=20

UNION

SELECT JOB FROM EMP WHERE DEPTNO=30

CREATE VIEW EMP_MAX_SAL

AS

SELECT DEPTNO, MAX(SAL) AS [HIGH SAL] FROM EMP GROUP BY DEPTNO

-If we want to perform manipulations on the Complex Views we have the following restrictions:

• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
• The columns being modified in the view must reference the underlying data in the table columns directly. They cannot be derived in any other way, such as through:
  • An aggregate function
  • A computation; the column cannot be computed from an expression using other columns. Columns formed using set operators  amount to a computation and are also not updatable.
• The columns being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.

-We can also classify views as Updateable Views and Non Updateable Views:

-          A View, which allows manipulations on it, is known as Updateable View.

-          A View, which will not allow manipulations on it, is known as Non Updateable View.

With Check Option:

-          Forces all data modification statements executed against the view to follow the criteria set within select statement.

-          The Clause specifies that DML operations are not allowed on rows that the View cannot Select

-          When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

CREATE VIEW SALES_EMP

AS

SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20

INSERT INTO SALES_EMP VALUES(1050, 'RAJU', 3500, 30)

-The above insert statement executes even if it does not satisfy the condition in the View, if this has to be restricted the view has to be created by using With Check Option clause.

ALTER VIEW SALES_EMP

AS

SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20

WITH CHECK OPTION

-If we want to make any modifications to the existing view we can use the alter view statement.

View Attributes:

Encryption: After creating a view if we want to see the view definition that can be found in the SYSCOMMENTS System Table.

SELECT TEXT FROM SYSCOMMENTS WHERE OBJECT_NAME(ID)='SALES_EMP'

If we want to hide the definition from other persons we can use the Encryption option while creating the view or alter the view after creation to add the clause:

ALTER VIEW SALES_EMP

WITH ENCRYPTION

AS

SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20

WITH CHECK OPTION

Schemabinding:

-          When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.

-          The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified.

-          When you use SCHEMABINDING, the select statement must include the two-part names (schema.object) of tables that are referenced.

-          We need to specify the column names individual in the select statement, cannot use "*" in the select statement.

-          All referenced objects 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.

CREATE VIEW EMP_BIND

WITH SCHEMABINDING

AS

SELECT EMNO, ENAME, JOB, MGR FROM DBO.EMP

-After the view is created EMP table cannot be dropped are the column referred in the views cannot be altered using the alter command.