SQL Views

View is nothing but saved SQL Statements or can be defined as an object that derives its data from one or more tables or in simple words we can say it as  virtual table.
(Views don't occupy storage space,Exception is indexed view..discussed in later posts).

Why to use Views:

• Query simplicity :-We may create a view using a complex query which can be easily queried later.
• security:-Views ensure the security of data by restricting access only to specific rows and columns.

Syntax:

• ToCreate:-->Create View ViewName as Query
• ToDrop:-->Drop View ViewName 
• To Modify:-->Alter View ViewName as Query

Options we can use while creating Views:
    2 important options that can be used while creating view are 
         SCHEMABINDING and ENCRYPTION.
1.SCHEMABINDING 

• This will  locks the tables being referred by the view and prevents any changes that may change the table schema.
• Requirements:-
• Objects have to be referred to by their owner names [two part name].
• SELECT * is not permitted.
• Example:- 
• Create View MyView With SchemaBinding as Select  CustomerName,CustomerId From  dbo.TblCustomer

(After creating the view, try to alter the table TblCustomer, you cant do it! This is the power of the SCHEMABINDING option.)
2.ENCRYPTION

• This will encrypts the definition of the view.Users will not be able to see the definition of the view after it is created.
• Example:
• Create View MyView With Encryption as Select CustomerName,CustomerId From dbo.TblCustomer
• Once Encrypted,there is no way to decrypt it again.So be careful when you are using this option.

Restrictions Imposed On Views are:

• A view can be created only in the current database.
• A view can be created only if there is a SELECT permission on its base table.
• Only select statement is allowed while creating view.
• A trigger cannot be defined on a view.
• The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.

Indexed Views:

• When a clustered index is created on the view, SQL Server immediately allocates storage space to store the results of the view. You can then treat the view like any other table by adding additional nonclustered indexes. 
• Requirements:- 
• View definition must always return the same results from the same underlying data. 
• Views cannot use non-deterministic functions. 
• The first index on a View must be a clustered, UNIQUE index. 
• If you use Group By, you must include the new COUNT_BIG(*) in the select list. 
• It must be created with SCHEMABINDING option. 
• View definition cannot contain the following 
• TOP 
• Text, ntext or image columns 
• DISTINCT 
• MIN, MAX, COUNT, STDEV, VARIANCE, AVG 
• SUM on a nullable expression 
• A derived table 
• Rowset function 
• Another view 
• UNION 
• Subqueries, outer joins, self joins 
• Full-text predicates like CONTAIN or FREETEXT 
• COMPUTE or COMPUTE BY 
• Cannot include order by in view definition

Updatable Views:

    Views are not only read-only but also updatable. However in order to create an updatable view, the SELECT statement which defines View has to follow several following rules:

• SELECT statement must not reference to more than one table. It means it must not contain more than one table in FROM clause, other tables in JOIN statement, or UNION with other tables.
• SELECT statement must not use GROUP BY or HAVING clause.
• SELECT statement must not use DISTINCT in the selection list.
• SELECT statement must not reference to the view that is not updatable
• SELECT statement must not contain any expression (aggregates, functions, computed columns…)