SqlTutorial-1 : Lesson-10 : Class-1 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Sql-VIEWS: Create, Alter, Updatable Views, Drop

I

n database theory, a view is the result set of a stored query. Unlike tables, views do not contain actual data instead they contain Select-query that used to define the View. When user executes a DML statement against a View, then Database Server reissues that DML command over underlying base Table. Hence, changes made to Views are actually changes underlying Table and changes made directly to underlying Table are auto-reflected dynamically when querying Views. For details see  Difference between View(Virtual Table) and Table. 

Views are one of the security measure offered by SQL Server and other RDBMS packages. View secures the data by means of Data Abstraction and Access Control by defining Permissions. Find details see How Views Provides Security

Views, a.k.a Virtual Tables, are nothing but stored-select-query, once view is defined  a user can use it by referencing its  name in Transact-SQL statements as the same way as a table is referenced. Views are still database objects i.e., persisting into the database.

INDEX

Overview

1. Introduction
2. Types of Views
   Updatable Vs Read-Only Views
3. Purpose of Views?Advantages of Views
4. How Views Provide Security?
5. Features and limitations of Views
6. What are Parameterized Views; 
7. View(Virtual) Vs Table

How-DO-I

1. CREATE Views 
2. CRUD-operations on Views
   (Updatable Views)
3. CREAT Read-Only Views
4. Creating Views with FLAVOURS:
   • CHECK-OPTION
   • WITH SCHEMABINDING
   • WITH ENCRYPTION
5. ALTER Views
6. DROP Views
7. CREATE INDEX on View

Overview

1.Introduction

• A view is a Stored Select statement used to expose filtered data. 
• A view is also called Virtual Table. 
• Views are not a copy of actual Table data. When you issue a query against a view, it internally executes underlying SELECT over base table(s).
• Changes made to base tables are reflect to their views when you query views.
• Dropping base table does not removes its defined Views, you need to remove views explicitly.
• Views looks much similar to table when you access them.
• Views are two types: Updatable, and Non-Updatable(Read-only). The View type is defined based on criteria of underlying query. 
• Updatable views allows you all DML operations to underlying table whereas Read-only
  does not.
• A view can be defined as Simple or Partitioned. A partitioned View is defined by using multiple SELECT ALL operations whereas Simple with Single SELECT.

2.Types of Views: Updatable Vs Read-Only Views

Based on Views Select query, views can be read-only or updatable. There is not standard Sql-Clause or Verb to define Read-only View or Updatable View.

Tehnically, " If the database system determines the reverse mapping from the view schema to its base table schema then the view is updatable otherwise Read-only". Updatable views allows INSERT, UPDATE, and DELETE operations whereas Read-Only does not.

Typically, views exposed as read-only in order to exposed data in desired manner with security.

A view that allows data manipulation to underlying table is said to be Updatable otherwise Readonly.

• Typically, updatable view is defined by using simple Select statement with single table.
• A View is non-updatable (Readonly)  if underlying query defined using
  1. Select with - DISTINCT
  2. Select with - Any function-f(n) such as scalar function or aggregate function.
  3. Select with -  Computed columns, e.g., col1+Col2 or expression, e.g., col1+”Welcome” . 
  4. Select with -  ORDER BY
  5. Select with - GROUP BY (Having)
  6. Select from with - Multiple tables
  7. Select From with - Joining operations 
  8. Select From with - Sets operations (Union, Intersect, Except etc).
  9. Select Where with - SubQueries.
  10. Select without - NOT NULL columns of underlying base table.

      Note: (1) Modification to multiple tables through a view is not possible.
                (2) If View allows all DML operations except a particular one,
                     then also view is considered as Read-Only.  
                (3) INSTEAD-OF trigger allowed on Views (Readonly or Updatable)

3.Purpose of Views? Advantages of Views

1. Data Abstraction: A view exposes processed data instead of raw data. e.g., Sum(salary).
2. Security: Typically, a view is used to hide the confidential data. Instead of exposing table directly, you can expose filtered data using views. Both column as well as row level filtering possible. 
3. Reusability: When you have a complex lengthy query and you need to execute it multiple times. So, instead of typing entire query each time, save it as a view execute multiple times. 
4. Manually Analyzing the Query: For analyzing a huge complex query, it is necessary to store the intermediate results of each sub-query in various Views, So that you can easily track and debug the complex query manually.Hence, it is also called stepping-stone for definign complex query.

4. How Views Provide Security?

There are three way-3 that views provide security:

1. Data Abstraction:
   1. A view exposes processed data instead of raw data. e.g., Sum(salary).
   2. A view exposes only Selected-filtered data instead of whole data.
   3. A view exposes combined data that joined filtered from multiple tables.
   So that user cannot determine underlying schema of the table.
   
2. Access Controls (Permission Sets): Some Database Engines, provides more access control measures than table to access the Views.
   
3. Encryption: You can encrypt the view in order to make underlying query unreadable.

5. Features and limitations of Views

Features

• A view can be created on another view. But nesting may not exceed 32 levels. 
• You can create index on views called Indexed Views. 
• A view can have maximum of 1,024 columns. 
• You can define the view using any valid SELECT statement including Expressions,
  functions etc.

Limitations:

• The query defining view cannot include
  
  1. COMPUTE 
  2. INTO clauses. (i.e., SELECT INTO) 
  3. ORDER BY without TOP clause.
• Unlike table, you cannot define full-text index on views. 
• Neither temporary views nor view on temporary tables are possible.

6. What are Parameterized Views;

A parameterized View contains a parameter in its SELECT-WHERE or SELECT-ORDER BY etc clause instead of constant value for filtering data. So that one parameterized View behaves as multiple views prvoided with different values to parameter.

Examples:

CREATE SQL VIEW XyzView
         AS SELECT * FROM Customers WHERE Customers.Country = ?cCountry

Parameter is prefixed with "?"

7. View(Virtual Tables) Vs Table

1. Data Abstraction: A view exposes a subset of data filtered by SELECT Query whereas Table exposes whole data.
2. Data Aggregation and relevancy: A view can be generated by JOINing multiple related tables to provide user with required data in requried format. A view contain SELECT-query processed result-set whereas Table contains raw-complete and unfiltered data. E.g., A view can contain formated date, caculated salary etc.
3. Data Storage: A view contains little space to store since it physically contains SELECT query instead actual data. Hence, it is also called virtual Table.
4. Trigger firing: A view (both Updatable and Readonly) supports only INSTEAD OF trigger whereas table supports both INSTEAD OF  and AFTER Trigger.The reason behind why views does not supports AFTER trigger is that some views are readonly they can't execute DML statement and AFTER trigger is fired only after suceeding of DML statment.
5. Dependency: A table is independent to other database objects whereas View is dependent on Table. Intgrity Constraints: No

HOW-DO-I

1. CREATE VIEW

Syntax:

CREATE VIEW  View_name[(column_name_1 [, column_name_2]...)] 
[WITH {SCHEMABINDING|ENCRYPTION}] 
AS 
"Select STATEMENT" 
[WITH CHECK OPTION]

A view always displays up-to-date data, since access views re-executes its underlying SELECT-query. Hence, every time a user gets updated data.

Examples:

1. Creating a simple view.

CREATE VIEW V11 AS SELECT * FROM Table1

2. Supplying Column Names for Views.

   CREATE VIEW V12(id, Name) AS SELECT id, first_name FROM Employee
   
   
   Explicit column names for view is required when underlying query returns duplicate columns from multiple tables)

3. Creating view using ORDER BY with TOP(without TOP, ORDER BY not possible)

CREATE VIEW V13 AS SELECT Top 10 Id FROM Table1 ORDER BY Id

4. PARITIONED VIEW (columns must be identical)

CREATE VIEW Sales_National
AS
   SELECT * FROM Sales_West
   UNION ALL
   SELECT * FROM Sales_North
   UNION ALL
   SELECT * FROM Sales_East
   UNION ALL
   SELECT * FROM Sales_South

2. CRUD-operations: Updatable Views Only

• Updatable Views allows all data manipulation to underlying table.
• Typically, updatable view is defined by using simple Select statement with single table.

Modification to multiple tables through a view is not possible.

Examples:

1. Creating Updatable-Views

   CREATE VIEW IBM_Billings
   AS
   (  SELECT BillingNumber, BillingDate, BillingTotal
      FROM Billings
      WHERE BankerID = (SELECT BankerID FROM Bankers
                                       WHERE BankerName='IBM' )
   )

   Inserting values

   INSERT INTO IBM_Billings (BillingNumber, BillingDate, BillingTotal)
   VALUES ('8', '2002-07-31', 417)

   Deleting values

   DELETE FROM IBM_Billings
   WHERE BillingNumber = '3'

   Updating View

   UPDATE IBM_Billings
   SET BillingTotal = 190000 WHERE BillingNumber= 1

3. Read-Only Views

A View is non-updatable if it is defined using:

1. Any function such as scalar function or aggregate function.
2. Joining operations and sets operations (Union, Intersect, Except etc).
3. Computed columns, e.g., col1+Col2 or expression, e.g., col1 + "Welcome"

Examples:

1. Creating Read-only Views

   CREATE TABLE one(col11 int NOT NULL, col12 int NOT NULL)
   CREATE TABLE two(col21 int NOT NULL, col22 int NOT NULL)
   
   CREATE VIEW one_two
   AS
   (  SELECT col11, col12, col21, col22
      FROM one LEFT JOIN two ON (col11=col21)
   )

   Insert into Non-Updatable View

   INSERT one_two (col11, col12) VALUES (1, 2) => O.K.
   SELECT * FROM one_two => 1 2 NULL NULL
   
   INSERT one_two (col11, col12, col21, col22)
   VALUES (1, 2, NULL, NULL) => Error

4. Creating Views with FLAVOURS:

4.1: CHECK-OPTION :

It restricts the View against DML operation on it

1. It enforces the WHERE condition as check constraint on View for INSERT and UPDATE operations. . e.g.,

   CREATE VIEW V11 AS
   SELECT * FROM t1
   WHERE col1<=3
   WITH CHECK OPTION

   Then following operation failed. But without CHECK OPTION no error.

   INSERT INTO V11(col1) VALUES(5) – Error
   
   
   In all respect, (i.e., WITH CHECK, SCHEMABINDING or ENCRYPTION), you can update base table directly without any fail.

2. Creating View WITH CHECK OPTION.

   CREATE VIEW RShippers_vw
   AS
   SELECT ShipperID, CompanyName, Phone
   FROM Shippers
   WHERE Phone LIKE '(503)%'
   WITH CHECK OPTION
   
   
   UPDATE RShippers_vw
   SET Phone = '(333) 555 9831'
   WHERE ShipperID = 1 => Error
   
   
   UPDATE RShippers_vw
   SET Phone = '(503) 555 9831'
   WHERE ShipperID = 1 => O.K

4.2: WITH SCHEMABINDING

It restricts the base table against DDL operations on it

Examples:

1. Creates a dependency such as underlying table (structure but not data) cannot be modified or deleted in any way that affects the view.

   • Consider what happened when a column referenced by a view gets renamed or deleted. It raises an error while executing that view. In order to avoid modifications to table structure where dependent views exist, use WITH SCHAMABINDING. 
   • SCHEMA BINDED base table modification is possible only when View definition gets change or view gets dropped. 
   • Table name must be fully qualified & column names of table
     must be specified (don’t use * )
   CREATE VIEW V12 WITH SCHEMABINDING
   AS
   Select col1,col2 from dbo.T1
   
   
   exec sp_rename 'dbo.T1.col1', 'column1', 'column' => Warning
   
   
   Sp_rename "object","newname", "typeofobject"
   
   Now, the col1, col2 cannot be renamed, deleted, or modified until dependency(binding) with view V12 get removed or view definition eliminate those columns.
   
   
   If owner of the database, and owner of the object are same, then the it qualified as dbo.objName

2. Creating View WITH SCHEMABINDING, base table structure cannot be modified.

CREATE TABLE employee(
   Iid INTEGER NOT NULL PRIMARY KEY,
   first_name VARCHAR(10),
   last_name VARCHAR(10),
   Salary DECIMAL(10,2) );

CREATE VIEW vEmployee WITH SCHEMABINDING
AS
  SELECT Id, first_name
  FROM dbo.Employee;

 Exec sp_rename 'dbo.employee.first_name', 'Ename', 'column'   => Warning! Error

4.3: WITH ENCRIPTION:

Encrypts the entries in sys.syscomments that contain the text of the CREATE VIEW statement.

1. Creating View with Encryption

CREATE VIEW V14 WITH ENCRYPTION AS SELECT * FROM Employee SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('V14')

Once the View is encrypted, then it cannot be decrypted back again.

5. ALTER VIEW

Syntax:

ALTER VIEW view_name[(column_name_1 [, column_name_2]...)]
 [WITH {SCHEMABINDING|ENCRYPTION}]
AS
  "select statement"
  [WITH CHECK OPTION]

Examples:

1. Creating view from Employee base table

   CREATE VIEW vProductCosts AS SELECT ID FROM Employee ALTER VIEW vProductCosts AS SELECT ID, First_Name FROM Employee
   
   
   ALTER VIEW is similar to CREATE VIEW just replace ALTER with CREATE.

6. DROP VIEW

Syntax:

DROP VIEW view_name

Examples:

1. Exception-Safe: Dropping Existing view

   IF OBJECT_ID('dbo.VCustsWithOrders') IS NOT NULL
   DROP VIEW dbo.VCustsWithOrders;

7. CREATE-INDEX On Views

Examples:

1. Creating NON-CLUSTERED Index on Views

   CREATE NONCLUSTERED INDEX NI_v_employee
   ON dbo.v_employee (First_Name).

2. Creating CLUSTERED-INDEX on views

   CREATE UNIQUE CLUSTERED INDEX UCI_v_employee
   ON dbo.v_employee (ID)