SQL SERVER – VIEWS  and its Limitations

SQL SERVER – VIEWS and its Limitations

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.