A View can be thought as either
a virtual table or a stored SQL Query in the database. Like a real table has
named columns and rows of data, a view also has set of named columns and rows
of data but the only difference is View never stores the data like a real
table. The data associated with the view is not stored in the view; it is
stored in the base table of view. We can create a new view on view. We can also
create trigger and Index on view.
We will go through the examples
and will understand the concept.
Let’s create a database
--CREATE A NEW DATABASE
TEACHMESQLSERVER
USE master
GO
IF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='TEACHMESQLSERVER')
DROP DATABASE TEACHMESQLSERVER
GO
CREATE DATABASE TEACHMESQLSERVER
GO
USE TEACHMESQLSERVER
GO
We have created our database;
now will create one table in this database.
-- CREATE A NEW TABLE EMP IN
TEACHMESQLSERVER DATABASE
CREATE TABLE EMP(EMPID INT PRIMARY KEY, EMPNAME VARCHAR(MAX), SALARY MONEY)
GO
INSERT INTO EMP VALUES(1,'A',100),(2,'k',1005),(3,'B',3100),(4,'C',1100),(5,'D',1009),(6,'E',1700),(7,'F',1040),(8,'G',100),(9,'H',1000)
GO
SELECT * FROM SYS.objects WHERE name='EMP'
Now we will create a view on
this EMP table.
Syntax to create view:
CREATE VIEW viewname
WITH <View Attributes> -- (This is Optional)
AS
SELECT STATEMENT -- (Your SQL Query, This is mandatory)
WITH CHECK OPTION -- (This is optional)
-- CREATE A VIEW ON THIS EMP TABLE
CREATE VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME FROM EMP
Congratulations you have
successfully create a simple view, you can find this view information from
system catalog Views. Click here to know
about System Catalog Views
What is stored in the database
is a SELECT STATEMENT that you can find using the below query.
GO
SELECT * FROM SYS.views WHERE name='EMPVIEW' -- Find THE OBJECT_ID AND PASS IT INTO THE
BELOW QUERY.
GO
SELECT * FROM SYSCOMMENTS WHERE ID=37575172 -- IN MY CASE IT IS 37575172
BUT IT WILL BE DIFFERENT IN YOUR CASE.
A User can use this view by
referencing the view name (EMPVIEW) in SQL statements in the same way a table
is referenced.
GO
SELECT * FROM EMPVIEW
If you will run the SELECT
STATEMENT (SELECT EMPID,EMPNAME FROM EMP)
which was used to create EMPVIEW then you will get the same above result set
which means the result set of the Select Statement forms the virtual table
which is returned by the view.
The rows and columns are
produced dynamically from the base tables (used in the select statement) when
the view is referenced.
As you can notice in our EMP
table we have three columns EMPID, EMPNAME, SALARY. But when you reference View
it only gives you EMPID and EMPNAME. It is because we have not included the
SALARY column at the time of CREATE VIEW. So you can say A View acts as a
filter on the underlying tables referenced in the view. We didn’t want other
users to know about the other EMP’s salary so we didn’t include the Salary
column in Create View Statement.
A View can be created from one
or more tables or views in the current or other database. There are no
restrictions on querying through views but there are few restrictions on
modifying data through View, Yes we can perform DML Operation on Views. When
you perform any DML operation it affects the referenced tables used in view.
Modify View
You can modify View using ALTER
COMMAND
ALTER VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO
SELECT * FROM EMPVIEW
GO
Congratulations you have
successfully modified the view.
Drop VIEW
If you want to delete view then
you can delete it using DROP COMMAND
DROP VIEW EMPVIEW
GO
SELECT * FROM SYS.views WHERE name='EMPVIEW'
Congratulations you have
successfully deleted the view.
We have gone through the basics
of view, let’s go through WITH <VIEW ATTRIBUTES> and WITH CHECK OPTION
VIEW ATTRIBUTES
This is optional; we have two
attributes ENCRYPTION and SCHEMABINDING. Let’s explore the use of these two attributes one by one.
1- ENCRYPTION
When you create any view
without ENCRYPTION in that case you can see view definition in Syscomments
system table, below example will explain it
CREATE VIEW EMPVIEW
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO
SELECT * FROM SYS.views WHERE name='EMPVIEW' -- Find THE OBJECT_ID AND PASS IT INTO THE
BELOW QUERY.
GO
SELECT * FROM SYSCOMMENTS WHERE ID=37575172 -- IN MY CASE IT IS 37575172
BUT IT WILL BE DIFFERENT IN YOUR CASE.
You can see view definition
from previous query in text column.
If you want to hide that
information from the system tables then you have to create view with ENCRYPTION, below example will explain it. Let’s alter the
view with ENCRYPTION option.
ALTER VIEW EMPVIEW
With encryption
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO
SELECT * FROM SYS.views WHERE name='EMPVIEW' -- Find THE OBJECT_ID AND PASS IT INTO THE
BELOW QUERY.
GO
SELECT * FROM SYSCOMMENTS WHERE ID=53575229 -- IN MY CASE IT IS 53575229
BUT IT WILL BE DIFFERENT IN YOUR CASE.
Congratulation! You have
successfully hid the view definition, check the text column returned from the
above query it is showing NULL
2- SCHEMABINDING
Suppose you have create a view
which has columns column1, column2, column3, what if user delete column3 from
the referenced table. In that case your view will not work it will give error
because when we created the view at that time it saved the view definition with
all three columns. Now one of the column has been dropped so when we will reference
the view it will search for all three columns but it will not find the column3.
How can we handle this kind of problem? Yes we can handle this problem what we
have to do is we have to create dependencies between view columns and
referenced table columns.
Using schemabinding option we
create this dependence. So when we create a view with schemabinding
option.
The base table or tables can’t
be modified in a way that would affect the view definition.
If you want
to modify the table then first either you have to drop the view or alter the
view without schemabinding option.
When you use Schemabinding ,
the select statement must include the two part names (schema.object) of tables
that are referenced.
We can’t use Select * when we
create a view with schemabinding option, we have to specify each column name.
All referenced
objects must be in same database.
Let’s go with example to
understand the concept, we will alter our previously created view.
GO
DROP VIEW EMPVIEW --If
view is already exists
GO
CREATE VIEW EMPVIEW --View
Without Schemabinding
AS
SELECT EMPID,EMPNAME,SALARY FROM EMP
GO
SELECT * FROM EMPVIEW
We have create a view without
schemabinding, now let’s drop salary column from the referenced table.
GO
ALTER TABLE EMP DROP COLUMN SALARY
You successfully dropped the
salary column, now let’s query against our EMPVIEW
Go
SELECT * FROM EMPVIEW
Your view didn't return any
result set but it gave below error
Msg 207, Level 16, State 1,
Procedure EMPVIEW, Line 3
Invalid column name 'SALARY'.
Msg 4413, Level 16, State 1,
Line 1
Could not use view or function
'empview' because of binding errors.
User was able to drop Salary
Column from the referenced table because our view was not created with
schemabinding option. Now let’s modify the view with schemabinding option.
GO
ALTER VIEW EMPVIEW --View
Schemabinding
WITH SCHEMABINDING
AS
SELECT EMPID,EMPNAME FROM DBO.EMP
GO
SELECT * FROM EMPVIEW
We have create a view
schemabinding, now let’s drop EMPNAME column from the referenced table.
GO
ALTER TABLE EMP DROP COLUMN EMPNAME
You can’t drop the column
because empname is dependent in view empview, you will get below error
Msg 5074, Level 16, State 1,
Line 1
The object 'EMPVIEW' is
dependent on column 'EMPNAME'.
Msg 4922, Level 16, State 9,
Line 1
ALTER TABLE DROP COLUMN EMPNAME
failed because one or more objects access this column.
Last time we successfully
dropped the salary column because we didn’t use schemabinding for view, but
this time we used schemabinding option that is why we were not able to drop the
column. If you want to drop the EMPNAME column
Then first you have to either
drop the view or alter view with no schemabinding option.
GO
ALTER VIEW EMPVIEW --View
Without Schemabinding
AS
SELECT EMPID,EMPNAME FROM DBO.EMP
GO
SELECT * FROM EMPVIEW
GO
ALTER TABLE EMP DROP COLUMN EMPNAME
You successfully dropped the
EMPNAME column, now you have to modify your view otherwise it will give error
i.e. Invalid column name EMPNAME
Refreshing Views
Suppose
you are working with non schema bind view if base table has been modified which
affected your view, In that case your view will not work it will give an error.
You have to refresh your View definition to make it workable.
There are couple of ways to
update View definition, one you can use alter command and update the view
definition another is using Stored Procedure you can refresh Views definition.
i.e
Execute sp_refreshview 'Your View Name'
I hope you enjoyed the tutorial;
There are couple of topics left
don't forget to visit below posts which are related to Views
1 comment:
Good Article ..
Post a Comment