View is a virtual table whose contents are defined by a query. Why we are using views?
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
Before Encryption:
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.