The Answer is,
- A security mechanism to allow users to access the data through views, without granting permissions to users to access the underlying tables.
- Complex queries can re-write to view, so that we can use simple queries to extract the data.
USE [MyExperiments] GO /****** Object: Table [dbo].[EmployeeDetails] Script Date: 01/11/2012 05:43:47 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[EmployeeDetails]( [ID] [int] NOT NULL, [Name] [varchar](50) NULL, [EmpType] [bit] NULL, [Designation] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Now I am inserting some data into the table.
--Inserting Data into the table insert into EmployeeDetails Select 1,'praveen',1,'DBA' union all select 2,'kiran',1,'juniorDBA' union all select 3,'Fazal',0,'QA Analyst' Creating a view for this table. --Creating a view Create view vw_EmpNameDesignation as select name,Designation from EmployeeDetails GO
Encryption option to restrict the view details:
If we want to secure metadata of the view, we can use Encryption option. We can restrict the users to see the create view statement from sys.syscomments
Here I am issuing to view the metadata
Select * from sys.syscomments
Applying Encryption option:
Here I am altering the view with Encryption option.
--Encrypting the view - Query statement Alter view vw_EmpNameDesignation with Encryption as select name,Designation from EmployeeDetails GO select * from sys.syscomments GO
Some of the Restriction of views:
- We should enable ANSII_NULL ON
- We should enable QUOTED_IDENTIFIER ON
- You cannot perform outer joins
- Indexed view should be created with schema binding option
- Repeated columns cannot use in select list of views.
- AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP are not allowed.
- We can’t use ROLLUP, HAVING and CUBE in Group by clause.