Creation of Views with different options

Bandi
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 1310 red flag

Views in SQL Server provides you to work with virtual tables, to hide complex queries or sensitive data.

Introduction

A view is a virtual table i.e. really nothing more than a logical representation of one or more tables in a database. We can categorize views as Read-only and Updatable Views based on the SELECT statement used while creating a view.


Syntax

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH  [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ]
[ ; ]
 
 ::=
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]    
}

Schema name: The view belongs to the specified schema. Otherwise it refers default schema of database if you haven’t specified the schema name while view creation.

Note: You MUST specify schema name if you wants to use SCHEMABINDING option

View Name: specify the name for a view which you are going to create

Column: It’s optional you can specify column names for a view. If you have any expressions/user-defined functions/calculations then you must have column names for the view; otherwise alias the column names as part of view definition (select statement)


Sample View Creation

IF OBJECT_ID ('dbo.vw_Sample', 'V') IS NOT NULL

  DROP VIEW dbo.vw_Sample ;

GO

CREATE VIEW vw_Sample ( Id, Name, Salary, AnnualPayment)

AS

SELECT EMPLOYEE_ID

            ,LAST_NAME

            ,SALARY

            ,SALARY*12

FROM DatabaseName.dbo.Employees

GO

SELECT * FROM vw_Sample

GO

--displays the view definition

SELECT OBJECT_NAME(id), text FROM sys.syscomments where OBJECT_NAME(id) = 'vw_Sample'

GO

DROP VIEW vw_Sample

GO


With Encryption/SchemaBinding/View_Metadata Options:

SCHEMABINDING:

The WITH SCHEMABINDING option says that all objects referenced inside the view’s definition would be attached with the view and nobody can drop those referenced objects as long as that particular view exists in database. Once view is dropped or view is altered with removal of WITH SCHEMABINDING option, the referenced objects can be dropped.

NOTE: To specify this option the base objects must be in the same database (i.e. object name must be in two-part format)

Without using scehmabinding option:

Use DatabaseName
GO
IF OBJECT_ID ('dbo.vw_Sample', 'V') IS NOT NULL
    DROP VIEW dbo.vw_Sample ;
GO
CREATE VIEW vw_Sample
 
AS
SELECT Cou_No,Cou_Desc, Cou_Cred FROM dbo.Course
GO
-- drop the base table refered in view
DROP TABLE dbo.Course
GO
 
SELECT * FROM vw_Sample -- leads to error because the base table is not available/existed

To prevent the above error or data loss, use schema binding option along with view..

With using scehmabinding option:

Use study
GO
IF OBJECT_ID ('dbo.vw_Sample', 'V') IS NOT NULL
    DROP VIEW dbo.vw_Sample ;
GO
CREATE VIEW vw_Sample
WITH SCHEMABINDING
AS
SELECT Cou_No,Cou_Desc, Cou_Cred FROM dbo.Courses
GO

Now the view is binded with table structure. Hence it throws error if you try to drop base table that refered in the view. In this way, we can precent the data loss. run below code to prove this.

DROP TABLE dbo.Courses -- gives error saying that this table/object refered in the view. We can't drop
GO

ENCRYPTION: View definition will be stored in the sys.syscomments. WITH ENCRYPTION option will encrypts the view definition so it wouldn’t allow users to reveal the view complex query/sensitive data inside the view.

IF OBJECT_ID ('dbo.vw_Sample', 'V') IS NOT NULL
    DROP VIEW dbo.vw_Sample ;
GO
CREATE VIEW vw_Sample ( Id, Name, Salary, AnnualPayment)
WITH ENCRYPTION
AS
SELECT EMPLOYEE_ID
            ,LAST_NAME
            ,SALARY
            ,SALARY*12
FROM DatabaseName.dbo.Employees
GO
--Will NOT display view definition
SELECT OBJECT_NAME(id), text FROM sys.syscomments where OBJECT_NAME(id) = 'vw_Sample'
GO

VIEW_METADATA: The view_metadata means that a client app will get meta-data back as if the view is actually a table. This will not display base table names as the object name from which the data is coming or data affected.

WITH CHECK OPTION: It forces all data modifications made through a view should satisfy the conditions in a view.

CREATE VIEW dbo.vw_HRDept
AS
SELECT EmployeeId, LastName, Salary
FROM Employees where department_id = 20
WITH CHECK OPTION
--Now try to update view for violating check constriant on the above View..
UPDATE dbo.vw_HRDept SET DepartmentID = 30 WHERE EmployeeID = 2
The above UPDATE statement fails beacuse of CHECK OPTION. As per CHECK OPTION condition the department id must be 20. But here the update statement is trying to modify the records with department id 30. So its violating condition of View. 

Conclusion

This article describes you the behaviour of different options available for views in SQL Server. There are some advantages for using views.
Security: we create views so that a user can read specific columns out of certain tables, but not all the data.
Simplification: sometimes, it’s easier to write a SELECT against one view than a SELECT against multiple tables, with joins – repeatedly.
Page copy protected against web site content infringement by Copyscape

About the Author

Bandi
Full Name: Chandrika Bandi
Member Level: Platinum
Member Status: Member,MVP
Member Since: 7/23/2013 5:52:37 AM
Country: India
[B]Mark This Response as Answer[/B] -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)