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.