Views in sql server

Views are virtual table that are compiled at runtime. We use that column in view that need to retrieve / query again and again. We can also make index, trigger on views. We can also write insert/ update query with a view like an actual table.

In the main reason of using view is for security purpose. Second reason is that the end user only knows a few columns. End users do not have knowledge about actual tables. By using view we can minimize writing long query again and again.

Syntax:

Syntax for create view is:

CREATE VIEW "VIEW NAME"

AS

[Select statements]

Syntax for alter view is:

ALTER VIEW "VIEW NAME"

WITH SCHEMABINDING

AS

[Select statements]

Syntax for drop view is:

DROP VIEW "VIEW NAME"

Note: We do not use BEGIN and END while creating/ altering view.

Example:

CREATE TABLE MST_EMPLOYEE_MASTER

(

      EMP_ID NUMERIC(18,0)IDENTITY(1,1),

      EMP_NAME VARCHAR(50) NOT NULL,

      DEPARTMENT_NAME VARCHAR(25),

      SALARY NUMERIC(18,2) DEFAULT 0,

      JOINING_DATE DATETIME NOT NULL DEFAULT GETDATE()

);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Shashi','Software Development',50000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Vikas','Software Development',100000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Prince','Software Testing',80000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Mathew','Software Testing',50000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Rahul','Software Testing',50000.00);

INSERT INTO MST_EMPLOYEE_MASTER (EMP_NAME, DEPARTMENT_NAME, SALARY) VALUES('Jonson','Human Resource',60000.00);

Example 1:

CREATE VIEW V_EMPLOYEE_MASTER

AS

      SELECT EMP_ID, EMP_NAME, DEPARTMENT_NAME FROM MST_EMPLOYEE_MASTER

And when select from view :

SELECT * FROM V_EMPLOYEE_MASTER;

Example 2:

ALTER VIEW V_EMPLOYEE_MASTER

AS

      SELECT EMP_ID, EMP_NAME, DEPARTMENT_NAME, SALARY FROM MST_EMPLOYEE_MASTER

And when select from view :

SELECT * FROM V_EMPLOYEE_MASTER;

Example 3:

DROP VIEW V_EMPLOYEE_MASTER

Some important points about View:

1.       We cannot use order by in view without using TOP clause.

2.       When we change table(s) schema from which view has been created, we have to refresh view for preventing unexpected result from view.

Syntax: EXECUTE SP_REFRESHVIEW 'View Name'

Example:EXECUTE SP_REFRESHVIEW 'V_EMPLOYEE_MASTER'

3.       In a view we can use maximum 1024 column(s).

4.       When we want to perform DML (INSERT/ UPDATE/ DELETE) operation on view then view should follow following condition:

i)                    Column(s) that we modify must be from only one table.

ii)                   Column(s) that we modify must not be created manually by other ways besides actual table column (actual column alias can be but not some operation performed and then created like column made from aggregate function like COUNT,MAX,AVG etc.)

iii)                 When we are doing INSERT operation all NOT NULL column(s) must be included except IDENTITY, DEFAULT column(s).

5.       Use WITH SCHEMABINDING when we want to prevent table dropped and view remain orphaned.

Syntax:

CREATE VIEW "VIEW NAME"

WITH SCHEMABINDING

AS

[Select statements]

Example:

CREATE VIEW V_EMPLOYEE_MASTER

WITH SCHEMABINDING

AS

SELECT EMP_ID, EMP_NAME, DEPARTMENT_NAME, SALARY FROM DBO.MST_EMPLOYEE_MASTER