Views in SQL Server

Views in SQL Server. This article explains about views in SQL server with sample SQL queries.

Introduction

 
In this article, we are going to discuss the Views in the SQL server. It is one of the important elements in the SQL server; it eliminates the difficulties in the business process. There are lots of significant improvements in the view, especially in the SQL server. They have introduced the indexed views and INSTEAD of Trigger on the permanent view. Let us discuss the views with more practical way.
 

Views in SQL Server

 
The view is a virtual table, which can have multiple columns from the one or more table. It can be used as 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 concerned database.
 
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 milestone in the view to allow the developers to create a 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 a security mechanism in web applications. When we use the original table in the web applications the hackers may drop the table. That time the original data will persist in the table.
 
When you want to hide the particular columns to the specific people then we can create a specialized view.
 

Encrypted View in SQL Server

 
The definition of the schema will be encrypted and stored as object in the database. This can be done using the ENCRYPTION option in the view creation.
  1. IF OBJECT_ID('[DBO].Vw_SqlObjects_Encrypted'IS NOT NULL  
  2. BEGIN  
  3.    DROP VIEW [DBO].Vw_SqlObjects_Encrypted  
  4.    PRINT '<< [DBO].Vw_SqlObjects_Encrypted View dropped >>'  
  5. END  
  6. GO  
  7. CREATE VIEW [DBO].Vw_SqlObjects_Encrypted  
  8. WITH ENCRYPTION  
  9. AS  
  10.    SELECT  
  11.        O.Object_ID  
  12.       ,O.Name  
  13.       ,'Type' = CASE O.type WHEN 'S' THEN 'Scalar Functions'  
  14.                           WHEN 'F' THEN 'Functions'  
  15.                           WHEN 'V' THEN 'Views'  
  16.                           WHEN 'PK' THEN 'Primary keys'  
  17.                           WHEN 'TR' THEN 'Triggers'  
  18.                           WHEN 'P' THEN 'Procedures'  
  19.                           WHEN 'U' THEN 'User Defined Functions'  
  20.                           WHEN 'TF' THEN 'Table Valued Functions'  
  21.                           WHEN 'IF' THEN 'Inline Functions' END  
  22.       ,O.create_date  
  23.       ,O.modify_date  
  24.       ,CASE WHEN SC.encrypted = 0 THEN 'No' ELSE 'Yes' END AS [IsEncrypted]  
  25.       ,SC.text  
  26.   FROM  
  27.       SYS.OBJECTS O  
  28.   INNER JOIN  
  29.       SYSCOMMENTS SC ON SC.id = O.object_id   
  30.  GO  
  31. IF OBJECT_ID('[DBO].Vw_SqlObjects_Encrypted'IS NOT NULL  
  32. BEGIN  
  33.    PRINT '<< [DBO].Vw_SqlObjects_Encrypted View created >>'  
  34. END  
  35. 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.
  1. SELECT text FROM SYSCOMMENTS WHERE id = OBJECT_ID('[DBO].Vw_SqlObjects_Encrypted')  
  2. SELECT definition FROM SYS.sql_modules WHERE object_id = OBJECT_ID('[DBO].Vw_SqlObjects_Encrypted')  
  3.   
  4. 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.
 
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 creating 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.
  1. USE [Northwind]  
  2. GO  
  3. IF OBJECT_ID('[DBO].vw_ViewProducts','V'IS NOT NULL  
  4. BEGIN  
  5.   DROP VIEW [DBO].vw_ViewProducts  
  6.   PRINT '<< [DBO].vw_ViewProducts view dropped.. >>'  
  7. END  
  8. GO  
  9. CREATE VIEW [DBO].vw_ViewProducts  
  10. AS  
  11.  SELECT   
  12.     ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,  
  13. UnitsOnOrder,ReorderLevel,Discontinued  
  14.  FROM Products  
  15. GO  
  16. IF OBJECT_ID('[DBO].vw_ViewProducts','V'IS NOT NULL  
  17. BEGIN  
  18.   PRINT '<< [DBO].vw_ViewProducts view created.. >>'  
  19. END  
  20. GO  
  21. --O/P  
  22. SELECT * FROM [DBO].vw_ViewProducts  
  23. --INSERT  
  24. INSERT INTO [DBO].vw_ViewProducts(ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,  
  25. UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued)  
  26. VALUES('Test View',1,2,'100 per bag',25.45,89,57,15,0)  
  27. --DELETE  
  28. DELETE 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. 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.
  1. IF EXISTS(SELECT OBJECT_ID FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].Vw_Product_Sales_Report',N'V'))  
  2. BEGIN  
  3.   DROP VIEW [DBO].Vw_Product_Sales_Report  
  4.   PRINT '<< [DBO].Vw_Product_Sales_Report view dropped >>'  
  5. END  
  6. GO  
  7. CREATE VIEW [DBO].Vw_Product_Sales_Report  
  8. WITH SCHEMABINDING  
  9. AS  
  10.   SELECT   
  11.       O.OrderID  
  12.      ,C.CustomerID  
  13.      ,C.CompanyName  
  14.      ,C.Address+', '+C.City AS [Customer Address]  
  15.      ,OD.ProductID  
  16.      ,P.ProductName  
  17.      ,OD.UnitPrice  
  18.      ,OD.Quantity  
  19.      ,(OD.UnitPrice * OD.Quantity) AS [Total]  
  20.      ,(OD.UnitPrice * OD.Quantity) * OD.Discount/100 AS [Discount]  
  21.    FROM  
  22.      [DBO].Orders O (NOLOCK)  
  23.    INNER JOIN [DBO]."Order Details" OD (NOLOCK) ON OD.OrderID = O.OrderID  
  24.    INNER JOIN [DBO].Customers C (NOLOCK) ON C.CustomerID = O.CustomerID  
  25.    INNER JOIN [DBO].Products P (NOLOCK) ON P.ProductID = OD.ProductID  
  26. GO  
  27. IF EXISTS(SELECT OBJECT_ID FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].Vw_Product_Sales_Report',N'V'))  
  28. BEGIN  
  29.   PRINT '<< [DBO].Vw_Product_Sales_Report view created >>'  
  30. END  
  31. GO  
Here the indexed view has created. When you retrieve the data from this table, it will execute like normal table.
 
There are some restrictions 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 definition.
 
Normally view cannot have the triggers but from the SQL server 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.
  1. USE [Northwind]  
  2. GO  
  3. CREATE TABLE EmployeeList  
  4. (  
  5.   iEmployeeID INT IDENTITY(1,1),  
  6.   vFirstName VARCHAR(25) NOT NULL,  
  7.   vLastName VARCHAR(25) NOT NULL,  
  8.   iDeptID INT,  
  9.   vAddress VARCHAR(25) NOT NULL,  
  10.   vCity VARCHAR(25) NOT NULL,  
  11.   vState VARCHAR(25) NOT NULL,  
  12.   vCountry VARCHAR(25) NOT NULL,  
  13. )  
  14. GO  
  15. USE [Master]  
  16. GO  
  17. CREATE TABLE Department  
  18. (  
  19.   iDeptID INT IDENTITY(1,1) PRIMARY KEY,  
  20.   vDeptName VARCHAR(50),  
  21.   vDeptDesc VARCHAR(25),  
  22.   vDeptAddedBy VARCHAR(50),  
  23.   vPostedDate DATETIME DEFAULT GETDATE()  
  24. )  
  25. GO  
  26. --SELECT * FROM Department  
  27. USE [Northwind]  
  28. GO  
  29. IF OBJECT_ID('[DBO].vw_LocalPartion_View','V'IS NOT NULL  
  30. BEGIN  
  31.   DROP VIEW [DBO].vw_LocalPartion_View  
  32.   PRINT '[DBO].vw_LocalPartion_View view dropped...'  
  33. END  
  34. GO  
  35. CREATE VIEW [DBO].vw_LocalPartion_View  
  36. AS  
  37. SELECT E.iEmployeeID,E.vFirstName+SPACE(1)+E.vLastName AS [Name],  
  38.        D.vDeptName,E.vAddress,E.vCity,E.vState  
  39. FROM EmployeeList E  
  40. --INNER JOIN Master..Department D ON D.iDeptID = E.iDeptID --Either one of the way will be used.  
  41. INNER JOIN Master.dbo.Department D ON D.iDeptID = E.iDeptID  
  42. GO  
  43. IF OBJECT_ID('[DBO].vw_LocalPartion_View','V'IS NOT NULL  
  44. BEGIN  
  45.   PRINT '[DBO].vw_LocalPartion_View view created...'  
  46. END  
  47. GO  
  48. --O/p  
  49. 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.
  1. sp_addlinkedserver 'Server name'  
The following system catalog table is used to see the list of linked servers.
  1. SELECT * FROM SYS.SERVERS  

INSTEAD OF Triggers on the Indexed View

 
Normally the triggers cannot be created on the view. But SQL server onwards we can create the INSTEAD OF trigger on the indexed views.
  1. USE [Northwind]  
  2. GO  
  3. IF OBJECT_ID('[DBO].[VW_Trigger_Example'IS NOT NULL  
  4. BEGIN  
  5.    DROP VIEW [DBO].[VW_Trigger_Example]  
  6.    PRINT '[DBO].[VW_Trigger_Example view dropped..'  
  7. END  
  8. GO  
  9. CREATE VIEW [DBO].[VW_Trigger_Example]  
  10. WITH SCHEMABINDING  
  11. AS  
  12.   SELECT P.ProductID,P.ProductName,P.SupplierID,  
  13.          OD.OrderID,OD.UnitPrice,OD.Quantity  
  14.   FROM [DBO].Products P  
  15.   INNER JOIN [DBO].[Order Details] OD ON OD.ProductID = P.ProductID  
  16. GO  
  17. IF OBJECT_ID('[DBO].[VW_Trigger_Example'IS NOT NULL  
  18. BEGIN  
  19.    PRINT '[DBO].[VW_Trigger_Example view created..'  
  20. END  
  21. GO  
  22. --SELECT * FROM VW_Trigger_Example  
  23. IF OBJECT_ID('[DBO].Tr_Delete_TriggerExample','TR'IS NOT NULL  
  24. BEGIN  
  25.   DROP TRIGGER [DBO].Tr_Delete_TriggerExample  
  26.   PRINT '[DBO].Tr_Delete_TriggerExample trigger dropped..'  
  27. END  
  28. GO  
  29. CREATE TRIGGER [DBO].Tr_Delete_TriggerExample  
  30. ON [DBO].VW_Trigger_Example  
  31. INSTEAD OF DELETE  
  32. AS  
  33. BEGIN  
  34.    PRINT '----------------------------------------'  
  35.    PRINT 'This is an example of INSTEAD OF Trigger'  
  36.    PRINT '----------------------------------------'  
  37.    SELECT TOP 1 * FROM DELETED  
  38. END  
  39. GO  
  40. IF OBJECT_ID('[DBO].Tr_Delete_TriggerExample','TR'IS NOT NULL  
  41. BEGIN  
  42.   PRINT '[DBO].Tr_Delete_TriggerExample trigger created..'  
  43. END  
  44. GO  
  45. --O/P  
  46. --SELECT * FROM [DBO].[VW_Trigger_Example] WHERE ProductID = 11  
  47. DELETE FROM [DBO].[VW_Trigger_Example] WHERE ProductID=11  

How to view the Created Views?

 
There are few ways to view the schema definition of the created views.
  1. SP_HELPTEXT vw_LocalPartion_View  
  2. SELECT id,text FROM SYSCOMMENTS WHERE id = OBJECT_ID('[DBO].vw_LocalPartion_View')  
  3. SELECT object_id,definition FROM SYS.SQL_MODULES WHERE OBJECT_ID = OBJECT_ID('[DBO].vw_LocalPartion_View')  

How to drop the View in SQL Server?

 
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 throw an error.
 
DROP VIEW VIEW_NAME
 

How to alter the view in SQL Server?

 
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]....
 

Conclusion

 
So far, we have seen the views in the SQL server. I hope that this has given enough idea about the views. If there is any correction or suggestions about this article please post your feedback.