Views and View Types in Sql Server 2005/2008

                                                 Views and View Types in Sql Server 2005/2008

View:The view is a virtual table, which can have the multiple columns from the one or more table. It can be used like the normal table. Normally view cannot store the data permanently in the table. When we create the view it stores the view definition schema as object under the concern database.

View Types:

There are three types of views in the sql server 2005.

They are 

1. Normal or Standard view
2. Indexed or permanent view
3. Partitioned view

            3a.local  Partitioned view
            3b.Global  Partitioned view

    

Let us see the syntax of the create view

CREATE VIEW View Name [Alias name1, name2,]

WITH ENCRYPTION

WITH SCHEMA BINDING

AS

SELECT statement [WITH CHECK OPTION]

The create view can be created with the view name and the alias can be given in the view name parameter parenthesis. The view schema can be stored in the encrypted format. Here is an option like SCHEMA BINDING; this is an important mile stone in the view to allow the developers to create the permanent view.

When to use VIEW?

When you have complex queries, that use many places in the stored procedures or functions, etc..,

It will be used as security mechanism in the web applications. When we use the original table in the web applications the hackers may drop the table. That time the original data will be persist in the table.

When you want to hide the particular columns to the specific people then we can create the specialized view.

Encrypted View:

The definition of schema will be encrypted and stored as object in the database. This can be done using the ENCRYPTION option in the view creation.

IF OBJECT_ID('[DBO].Vw_SqlObjects_Encrypted') IS NOT NULL
BEGIN   
DROP VIEW [DBO].Vw_SqlObjects_Encrypted  
 PRINT '<< [DBO].Vw_SqlObjects_Encrypted View dropped >>'
END
GO
CREATE VIEW [DBO].Vw_SqlObjects_Encrypted
WITH ENCRYPTION
AS  
 SELECT  O.Object_ID,O.Name,'Type' = CASE O.type

 WHEN 'S' THEN 'Scalar Functions'

 WHEN 'F' THEN 'Functions'          

 WHEN 'V' THEN 'Views'  

 WHEN 'PK' THEN 'Primary keys'

 WHEN 'TR' THEN 'Triggers' 

 WHEN 'P' THEN 'Procedures' 

 WHEN 'U' THEN 'User Defined Functions'   

 WHEN 'TF' THEN 'Table Valued Functions'

 WHEN 'IF' THEN 'Inline Functions'  END

 ,O.create_date ,O.modify_date ,

CASE WHEN SC.encrypted = 0 THEN 'No' ELSE 'Yes' END AS [IsEncrypted]      ,SC.text 

 FROM  

 SYS.OBJECTS O  INNER JOIN  

 SYSCOMMENTS SC ON SC.id = O.object_id  GO

IF OBJECT_ID('[DBO].Vw_SqlObjects_Encrypted') IS NOT NULL

BEGIN  

 PRINT '<< [DBO].Vw_SqlObjects_Encrypted View created >>'

END

GO

Now if you want to see the view schema definition for the above view is not possible. We have stored in the encrypted format. This is a significant option to hide the important calculations inside the view from the others.

In case of any alter in the view must be stored externally somewhere else.

SELECT text FROM SYSCOMMENTS 

WHERE id = OBJECT_ID('[DBO].Vw_SqlObjects_Encrypted')

SELECT definition FROM SYS.sql_modules 
WHERE object_id =OBJECT_ID('[DBO].Vw_SqlObjects_Encrypted')

sp_helptext Vw_SqlObjects_Encrypted

If you execute the above queries then it will say like view is encrypted.

There are three types of views in the sql server 2005.

They are 

1. Normal or Standard view
2. Indexed or permanent view
3. Partitioned view

Normal or Standard view:

This view is most frequently used by the developers. When create the view the schema will be stored an object in the database. When we retrieve the content from this virtual table, it will be executed the schema and stored the data from the parent table.

Here if you have the result from the same table then it can be updated and inserted. The deleted row will be reflected in the original table.

USE [Northwind]GOIF OBJECT_ID('[DBO].vw_ViewProducts','V') IS NOT NULL

BEGIN 

 DROP VIEW [DBO].vw_ViewProducts  

PRINT '<< [DBO].vw_ViewProducts view dropped.. >>'

END

GO

CREATE VIEW [DBO].vw_ViewProducts

AS

 SELECT     ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued 

FROM Products

GO

IF OBJECT_ID('[DBO].vw_ViewProducts','V') IS NOT NULL

BEGIN  

PRINT '<< [DBO].vw_ViewProducts view created.. >>'

END

GO

--O/P

SELECT * FROM [DBO].vw_ViewProducts

--INSERT

INSERT INTO[DBO].vw_ViewProducts(ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued)

VALUES('Test View',1,2,'100 per bag',25.45,89,57,15,0)

--DELETEDELETE FROM [DBO].vw_ViewProducts WHERE ProductID = 81

Here you can do the DML operations in the view when you have only one table.

Indexed views:

The indexed or permanent view is one of the new features introduced in the sql server 2005 version. We have seen that the view only store the schema definition and it will get execute and load the data into the virtual table at the time of view used. But this view creates the permanent view and we can create the indexes on the table. It allows us to create the instead of trigger.

The indexed view can be created with the WITH SCHEMA BINDING option while creating the view.

The indexed view has some restrictions like cannot use the TOP, DISTINCT, UNION, ORDER BY and aggregate functions.

It allows us to use the GROUP BY statement but we cannot use COUNT statement. Instead of that COUNT_BIG statement can be used.

IF EXISTS(SELECT OBJECT_ID FROM SYS.OBJECTS 
WHERE OBJECT_ID = OBJECT_ID(N'[DBO].Vw_Product_Sales_Report',N'V'))
BEGIN 
 DROP VIEW [DBO].Vw_Product_Sales_Report
  PRINT '<< [DBO].Vw_Product_Sales_Report view dropped >>'
END
GO
CREATE VIEW [DBO].Vw_Product_Sales_Report
WITH SCHEMABINDINGAS  
SELECT       O.OrderID,C.CustomerID  ,C.CompanyName,
C.Address+', '+C.City AS [Customer Address] ,OD.ProductID  ,P.ProductName     ,OD.UnitPrice     ,OD.Quantity     ,(OD.UnitPrice * OD.Quantity) AS [Total]     ,(OD.UnitPrice * OD.Quantity) * OD.Discount/100 AS [Discount]
   FROM     [DBO].Orders O (NOLOCK)  
 INNER JOIN [DBO]."Order Details" OD (NOLOCK) ON OD.OrderID = O.OrderID 
 INNER JOIN [DBO].Customers C (NOLOCK) ON C.CustomerID = O.CustomerID  
 INNER JOIN [DBO].Products P (NOLOCK) ON P.ProductID = OD.ProductID
GO
IF EXISTS(SELECT OBJECT_ID FROM SYS.OBJECTS 
WHERE OBJECT_ID = OBJECT_ID(N'[DBO].Vw_Product_Sales_Report',N'V'))
BEGIN 
PRINT '<< [DBO].Vw_Product_Sales_Report view created >>'ENDGO
Here the  indexed view has created. When you retrieve the data from this table, it will execute like normal table.

There are some retrictions while creating this indexed view like the name of the view must be two part name and we cannot use select * in the view schema defintion.

that is dbo.table name must be use to avoid two part name error while creating.
Normally view cannot have the triggers but from the sql server 2005 onwards We can create the Instead of trigger on the instead of trigger.

Partitioned Views:
The partitioned view and its execution is like normal view. It will work across the database and across the server.

There are two types of Partitioned views. They are 

1. Local Partitioned View
2. Global Partitioned View

1. Local Partitioned View:

The local partitioned view can be created within same server but different database.

The view schema definition will be stored in the executed database. But when we try to retrieve the data from the table, it has to execute the schema definition internally and load the data.

Let us see an example.

USE [Northwind]GO

CREATE TABLE EmployeeList(  iEmployeeID INT IDENTITY(1,1),  vFirstName VARCHAR(25) NOT NULL,  vLastName VARCHAR(25) NOT NULL,  iDeptID INT,  vAddress VARCHAR(25) NOT NULL,  vCity VARCHAR(25) NOT NULL,  vState VARCHAR(25) NOT NULL,  vCountry VARCHAR(25) NOT NULL,)

GO

USE [Master]

GO

CREATE TABLE Department(  iDeptID INT IDENTITY(1,1) PRIMARY KEY,  vDeptName VARCHAR(50),  vDeptDesc VARCHAR(25),  vDeptAddedBy VARCHAR(50),  vPostedDate DATETIME DEFAULT GETDATE())GO--SELECT * FROM Department

USE [Northwind]

GO

IF OBJECT_ID('[DBO].vw_LocalPartion_View','V') IS NOT NULLBEGIN  DROP VIEW [DBO].vw_LocalPartion_View 

 PRINT '[DBO].vw_LocalPartion_View view dropped...'

END

GO

CREATE VIEW [DBO].vw_LocalPartion_View

AS

SELECT E.iEmployeeID,E.vFirstName+SPACE(1)+E.vLastName AS [Name],       D.vDeptName,E.vAddress,E.vCity,E.vState

FROM EmployeeList E

--INNER JOIN Master..Department D ON D.iDeptID = E.iDeptID --Either one of the way will be used.

INNER JOIN Master.dbo.Department D ON D.iDeptID = E.iDeptID

GO

IF OBJECT_ID('[DBO].vw_LocalPartion_View','V') IS NOT NULL

BEGIN  

PRINT '[DBO].vw_LocalPartion_View view created...'

END

GO

--O/p

SELECT * FROM [DBO].vw_LocalPartion_View

2. Global Partitioned View

The global Partitioned view will work across the server. The view can be created to join the table across the server.

The accessing format will be like this.

[Server Name].  Database Name. Table Name

When we execute the view if it is not linked with the current server then it will ask us to link the external server.

The following system stored procedure will be used to link the server.

sp_addlinkedserver 'Server name'

The following system catalog table is used to see the list of linked servers.

SELECT * FROM SYS.SERVERS
INSTEAD OF Triggers on the Indexed View

Normally the triggers cannot be created on the view. But sql server 2005 onwards we can create the INSTEAD OF trigger on the indexed views.

USE [Northwind]

GO

IF OBJECT_ID('[DBO].[VW_Trigger_Example') IS NOT NULL

BEGIN   

DROP VIEW [DBO].[VW_Trigger_Example]   

PRINT '[DBO].[VW_Trigger_Example view dropped..'

END

GO

CREATE VIEW [DBO].[VW_Trigger_Example]

WITH SCHEMABINDINGAS  

SELECT P.ProductID,P.ProductName,P.SupplierID,         OD.OrderID,OD.UnitPrice,OD.Quantity  

FROM [DBO].Products P  

INNER JOIN [DBO].[Order Details] OD ON OD.ProductID = P.ProductID

GO

IF OBJECT_ID('[DBO].[VW_Trigger_Example') IS NOT NULL

BEGIN  

 PRINT '[DBO].[VW_Trigger_Example view created..'

END

GO

--SELECT * FROM VW_Trigger_Example

IF OBJECT_ID('[DBO].Tr_Delete_TriggerExample','TR') IS NOT NULL

BEGIN  

DROP TRIGGER [DBO].Tr_Delete_TriggerExample 

 PRINT '[DBO].Tr_Delete_TriggerExample trigger dropped..'ENDGOCREATE TRIGGER [DBO].Tr_Delete_TriggerExampleON [DBO].VW_Trigger_ExampleINSTEAD OF DELETEASBEGIN   PRINT '----------------------------------------'  

 PRINT 'This is an example of INSTEAD OF Trigger'  

 PRINT '----------------------------------------' 

  SELECT TOP 1 * FROM DELETED

END

GO

IF OBJECT_ID('[DBO].Tr_Delete_TriggerExample','TR') IS NOT NULL

BEGIN  

PRINT '[DBO].Tr_Delete_TriggerExample trigger created..'

END

GO

--O/P--SELECT * FROM [DBO].[VW_Trigger_Example] WHERE ProductID = 11

DELETE FROM [DBO].[VW_Trigger_Example] WHERE ProductID=11

How to view the Created Views?

There are few ways to view the scehema definition of the created views.

SP_HELPTEXT vw_LocalPartion_View

SELECT id,text FROM SYSCOMMENTS 

WHERE id = OBJECT_ID('[DBO].vw_LocalPartion_View')

SELECT object_id,definition FROM SYS.SQL_MODULES 

WHERE OBJECT_ID = OBJECT_ID('[DBO].vw_LocalPartion_View')

How to drop the View?

If you want to drop the view then you can use the following statement. When you drop the table underlying view will not be deleted. But if you run that view it will thrown an error.

DROP VIEW VIEW_NAME

How to alter the view?

If you want to do changes in the created views then you can alter the view whatever you want to view the same view name.

ALTER VIEW VIEW_NAME

AS

SELECT [Columns List]....

VIEWs:

Ref:
http://sqlhints.com/category/sql-server/views/

Views are nothing but saved SQL statements, and are sometimes referred as Virtual Tables. Keep in mind that Views cannot store data rather they only refer to data present in tables.

Benefits of Views:A view can be useful when there are multiple users with different levels of access, who all need to see portions of the data in the database (but not necessarily all the data). Views can do the following:

• Restrict access to specific rows in a table
• Restrict access to specific columns in a table
• Join columns from multiple tables and present them as though they are part of a single table
• Present aggregate information (such as the results of the COUNT function)

Lets checkout the basic syntax for creating a view:
CREATE VIEW <View_Name>
AS
<SELECT Statement>

• You cant able to use DML operations inside View 

alter view vw_custinfo

as

update Custtemp set custtemp='TEst' where CustomerID in 

(SELECT  top 1 CustomerID

from dbo.Customers)

Msg 156, Level 15, State 1, Procedure vw_custinfo, Line 4

Incorrect syntax near the keyword 'update'.

• you can able add new rows in view it reflected in table

• Below is an example view where it returns the data from multiple tables by joining:

Create View vwGetCustomerOrders
AS
 SELECT C.FirstName,O.OrderId
 FROM Customers C
   INNER JOIN Orders O
    ON C.CustomerId = O.CustomerId
GO

Select * from vwGetCustomerOrders

• Addition of New Column’s in the Underlying Table will not automatically reflect in the existing views you have to refresh view or drop and recreate it to reflect those column in view

Let us prove this behaviour by 

creating a view vwGetCustomers  which returns all customer details with all the columns in the customer table: 

Create View vwGetCustomers
AS
SELECT *
FROM Customers
GO

Select * FROM vwGetCustomers

Now add one more column Country to the Customers table:
ALTER Table Customers
ADD Country Varchar(30)

Execute the below statement and observe that the new column country added in the Customers table is not present in the result.
SELECT * From vwGetCustomers

following two way to reflect this new column in the view is to drop and create back the view as below

Drop View vwGetCustomers

or

if you use

Sp_refreshview ‘vwGetCustomers’

Then new column should be reflected in result for view.

• When the View is created on Multi tables we can not delete from the View, though we can update it which will affect the corresponding table column value.

Create View vwGetCustomerOrders

AS

 SELECT C.CustomerId,O.OrderId

 FROM Customers C

   INNER JOIN Orders O

    ON C.CustomerId = O.CustomerId

GO

• You can able to join view with other tables using join

Select * from vwGetCustomerOrders vwco

join Customers c on vwco.customerid=c.CustomerID 

• you can able to use it on cross join

Select * from vwGetCustomerOrders vwco

cross join Customers c where vwco.customerid=c.CustomerID 

• View can be able to create index but it have to create using with Schemabinding option 

The indexed view can be created with the WITH SCHEMA BINDING option while creating the view.

The indexed view has some restrictions like cannot use the TOP, DISTINCT, UNION, ORDER BY and aggregate functions.

It allows us to use the GROUP BY statement but we cannot use COUNT statement. Instead of that COUNT_BIG statement can be used.

•         while you create Indexed view object name should be dbo.tablename if you omit dbo. before table name it throws error

Cannot schema bind view 'dbo.viewname' because name 'tablename' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
see below

• while you create indexed view tables are belong to same database otherwise it throws error as below

Cannot schema bind view 'dbo.objectname' because name 'dbname.dbo.tablename' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

So, After following above rule you can able to create view with index(indexed view) as shown below

ALTER View dbo.vwGetCustomerOrdersDetwithtrigex with Schemabinding 
AS
 SELECT C.CustomerId,O.OrderId,C.City,O.OrderDate
 FROM dbo.Customers C
   INNER JOIN dbo.Orders O
    ON C.CustomerId = O.CustomerId

• AFTER triggers cannot be defined on views. we can define instead of triggers on view kindly see below for your more details

INSTEAD OF Triggers on the Indexed View

Normally the triggers cannot be created on the view. But sql server 2005 onwards we can create the INSTEAD OF trigger on the indexed views.

USE [Northwind]

GO

IF OBJECT_ID('[DBO].[VW_Trigger_Example') IS NOT NULL

BEGIN   

DROP VIEW [DBO].[VW_Trigger_Example]   

PRINT '[DBO].[VW_Trigger_Example view dropped..'

END

GO

CREATE VIEW [DBO].[VW_Trigger_Example]

WITH SCHEMABINDINGAS  

SELECT P.ProductID,P.ProductName,P.SupplierID,         OD.OrderID,OD.UnitPrice,OD.Quantity  

FROM [DBO].Products P  

INNER JOIN [DBO].[Order Details] OD ON OD.ProductID = P.ProductID

GO

IF OBJECT_ID('[DBO].[VW_Trigger_Example') IS NOT NULL

BEGIN  

 PRINT '[DBO].[VW_Trigger_Example view created..'

END

GO

--SELECT * FROM VW_Trigger_Example

IF OBJECT_ID('[DBO].Tr_Delete_TriggerExample','TR') IS NOT NULL

BEGIN  

DROP TRIGGER [DBO].Tr_Delete_TriggerExample 

 PRINT '[DBO].Tr_Delete_TriggerExample trigger dropped..'

END

GO

CREATE TRIGGER [DBO].Tr_Delete_TriggerExample

ON [DBO].VW_Trigger_Example

INSTEAD OF DELETEASBEGIN   

PRINT '----------------------------------------'  

PRINT 'This is an example of INSTEAD OF Trigger'  

PRINT '----------------------------------------' 

SELECT TOP 1 * FROM DELETED

END

GO

IF OBJECT_ID('[DBO].Tr_Delete_TriggerExample','TR') IS NOT NULL

BEGIN  

PRINT '[DBO].Tr_Delete_TriggerExample trigger created..'

END