All about View in SQL Server

View::Any relation which is not part of logical model,but is made visible to a user as a virtual relation, is called a view.

--CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]

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

AS select_statement [ ; ]

[ WITH CHECK OPTION ]

<view_attribute> ::=

{

[ ENCRYPTION ]

[ SCHEMABINDING ]

[ VIEW_METADATA ] }

-----------------------------------------------------------------------------------------

ENCRYPTION :The definition of an encrypted view is not visible to anyone, including a member of the sysadmin fixed server role.cannot decrypt the definition.

SCHEMABINDING :cannot drop any tables, views, or functions referenced by the view without first dropping the view.

VIEW_METADATA :

-----------------

Example:CREATE VIEW v_CustomerAddress

AS

SELECT a.CustomerID, a.CustomerName, c.AddressLine1, c.AddressLine2, c.AddressLine3,

c.City, d.StateProvince, c.PostalCode, e.Country

FROM dbo.Customer a INNER JOIN dbo.CustomerToCustomerAddress b ON a.CustomerID =b.CustomerID

INNER JOIN dbo.CustomerAddress c ON b.CustomerAddressID = c.CustomerAddressID

INNER JOIN dbo.StateProvince d ON c.StateProvinceID = d.StateProvinceID

INNER JOIN dbo.Country e ON c.CountryID = e.CountryID;

------------------------------------------------------

--Quick Check

¦ What are the restrictions on the SELECT statement within a view?

--Quick Check Answer

¦ COMPUTE or COMPUTE BY clauses are not allowed. You cannot use the

INTO keyword or OPTION clause. Temporary tables and table variables

cannot be referenced. An ORDER BY clause cannot be specified unless the

TOP operator is also used.

--*****************************************--

--Create an Updateable View

CREATE VIEW dbo.v_Customer

AS

SELECT CustomerID, CustomerName, CreditLine, AvailableCredit

FROM dbo.Customer

WHERE CreditLine > 1000

WITH CHECK OPTION;

----

INSERT INTO dbo.Customer

(CustomerName, CreditLine)

VALUES('Customer1',5000);

----------**********************************---------

--Indexed View/Materialized View

Prerequisites for an Indexed View

In theory, creating an indexed view is simply a process of creating a view and then creating

a clustered index on the view. In practice, the process is not so straightforward.

To create an indexed view, the base tables for the view must meet many criteria. The

view then has additional restrictions. Finally, the index has even more restrictions.

The purpose of all these restrictions is to ensure that SQL Server can perform a consistent

calculation. An indexed view, also called a materialized view, causes SQL

Server to execute the SELECT statement in the view definition. SQL Server then builds

a clustered index on the view’s results, and stores the data and index within the database.

As you change data in the base tables, SQL Server propagates these changes to

the indexed view. If the result of the view could change from one execution to another

or could change if different query options were set, the entire set of data SQL Server

calculated and stored would be invalidated. Therefore, all the operators or functions

that can cause varying results are disallowed.

-----------------------

--Some examples of these restrictions are as follows:

* The SELECT statement cannot reference other views.

* All functions must be deterministic. For example, you cannot use getdate()

because every time it is executed, it returns a different date result.

* AVG, MIN, MAX, and STDEV are not allowed.

-----------*************************--------------

--Quick Check

Q)What is the difference between a regular view and an indexed view?

--Quick Check Answer

A)A regular view is a SELECT statement that is referenced by a name and

stored in SQL Server. It does not contain any data. An indexed view is a

view that has a clustered index created against it, which causes SQL Server

to materialize and store the results of the query defined in the view on disk.

An indexed view must meet very stringent requirements for the view, the

base tables that the view references, and the index on the view.

----------------------------------------------

--Create an Indexed View

In this practice, you create an indexed view in the AdventureWorks database.

1. If necessary, launch SSMS, connect to your instance, open a new query window,

and change the context to the AdventureWorks database.

2. Create an indexed view called Orders by executing the following code:

--Set the options to support indexed views.

SET NUMERIC_ROUNDABORT OFF;

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,

QUOTED_IDENTIFIER, ANSI_NULLS ON;

GO

--Create view with schemabinding.

IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL

DROP VIEW Sales.vOrders ;

GO

CREATE VIEW Sales.vOrders

WITH SCHEMABINDING

AS

SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,

OrderDate, ProductID, COUNT_BIG(*) AS COUNT

FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o

WHERE od.SalesOrderID = o.SalesOrderID

GROUP BY OrderDate, ProductID;

GO

--Create an index on the view.

CREATE UNIQUE CLUSTERED INDEX IDX_V1

ON Sales.vOrders (OrderDate, ProductID);

GO

3. Execute the following queries, which use the indexed view even though the view

is not explicitly referenced in the queries:

SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev,

OrderDate, ProductID

FROM Sales.SalesOrderDetail AS od

JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID

AND ProductID BETWEEN 700 and 800

AND OrderDate >= CONVERT(datetime,'05/01/2002',101)

GROUP BY OrderDate, ProductID

ORDER BY Rev DESC;

SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev

FROM Sales.SalesOrderDetail AS od

JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID

AND DATEPART(mm,OrderDate)= 3

AND DATEPART(yy,OrderDate) = 2002

GROUP BY OrderDate

ORDER BY OrderDate ASC;

-----------------------------------------

--NOTE:

*You create an indexed view by creating a clustered index on the view.

* By creating a clustered index on a view, SQL Server stores the result set of querying

the view on disk, which can dramatically improve performance, especially

for queries that perform aggregations or computations.

* If you are using SQL Server 2005 Enterprise Edition, the query optimizer will

automatically rewrite a query to use an indexed view if it determines that the

indexed view would be more efficient than the base table in satisfying the query.