View in Database term is a virtual table, it doesn’t exist physically but query to construct the result set is actually stored there. The View is created on the top a table or in advanced cases a view can use more than one table which is referred as partitioned view as it’s column are partitioned among underlying tables.
How to create a simple view?
First we start with our trademark table
CREATE TABLE Course
(
CourseId int Primary key identity(1,1),
CourseName Varchar(200) not null,
StartDate datetime,
EndDate datetime,
Deleted char(1) Constraint Check_Deleted CHECK ( Deleted in ('Y','N'))
)
Now add few records to this table
insert into CourseUpdatableView(CourseName,StartDate,EndDate,Deleted)values('c',getdate(),dateadd(month,10,getdate()),'N')
insert into CourseUpdatableView(CourseName,StartDate,EndDate,Deleted)values('cplus',getdate(),dateadd(month,10,getdate()),'N')
insert into CourseUpdatableView(CourseName,StartDate,EndDate,Deleted)values('java',getdate(),dateadd(month,10,getdate()),'N')
insert into CourseUpdatableView(CourseName,StartDate,EndDate,Deleted)values('csharp',getdate(),dateadd(month,10,getdate()),'N')
Let’s Create a Simple View that will contain CourseName, StartDate, EndDate, Deleted columns from Course table.
As follows:
create view CourseUpdatableView as select CourseName,StartDate,EndDate,Deleted from Course
Here to create our view CourseUpdatableView we used following select query
select CourseName,StartDate,EndDate,Deleted from Course
There are few restriction on how this select query should look like so that one can create a view from it.
Now we will Alter the view CourseUpdatableView. As follows
ALTER view dbo.CourseUpdatableView as select * from dbo.Course
Now we Modify our underlying table specifically by adding one more column
alter table course add courseFees float
Now lets check if our view is aware of this new additional column we just added.
Run following query
Select * from CourseUpdatableView
You will notice that the new column doesn’t appear in select list.Why this a happened because when we have underlying query of view as
Select * from Course
The sql-server doesn’t save this query as it is ,it transalated it explicitly into list of column that were present at that time in table hence addition of new column doesn’t have any effect on our view.
Now Let’s Try to update the CouseFee column through our view as follows
update CourseUpdatableView set CourseFee=6000
This will raise an error ‘Invalid column name 'CourseFee'.This is because we are trying to update a column from underlying table which is not part of columns in our view.
This can be verified as follows run following query try updating a column that is in select list of our view.
update CourseUpdatableView set Deleted='N'
Here the query update the Deleted column for each record in our table but point to note that we havn’t put any where condition in out select query of view further ‘Deleted’ column do exist in our list of column in View.
Now Lets Go For View That has condition on it
Here is the code to create one
CREATE VIEW CourseUpdatableViewWithCondition As
select
CourseName,
StartDate,
EndDate,
Deleted,
CourseFee
From
dbo.Course
Where
CourseFee >8000
Add few records into table with course fee less than or equal to 8000 if it won’t have already.
Now when we run query bellow
update CourseUpdatableViewWithCondition Set Deleted='Y' Where CourseFee = 8000
No row get affected even though table contain few records with course fee 8000 or less
Why this happened?
Try to run
Select * from CourseUpdatableViewWithCondition
In the output you won’t see any record whose course_fee is 8000 or less.from this we can conclude that we can update only those records in underying table using insert on view which we can appears in select on our view after enforing the condition we put in where clause of view here in this case it is ‘CourseFee >8000’
Now Try to Insert a record on view which is violating the where clause in view.As follows
insert into CourseUpdatableViewWithCondition(CourseName,StartDate,EndDate,Deleted,CourseFee)values('j2me',getdate(),dateadd(year,1,getdate()),'N','6000')
You will notice that record get inserted into table,but it will not appear in ‘select * from CourseUpdatableViewWithCondition’ result set.
Can We control Such Weird Insertion ?
Here comes in picture views with check option,we tell the system not allow insert on view whose record will never figure out in select on ‘CourseUpdatableViewWithCondition’
ALTER VIEW CourseUpdatableViewWithCondition As
select
CourseName,
StartDate,
EndDate,
Deleted,
CourseFee
From
dbo.Course
Where
CourseFee >8000
with CHECK OPTION
try running insert into this view whose course_fee is less than 8000
insert into CourseUpdatableViewWithCondition(CourseName,StartDate,EndDate,Deleted,CourseFee)values('python',getdate(),dateadd(year,1,getdate()),'N','6000')
The error will be thrown instructing about failure to meet where condition in our case ‘CourseFee >8000’ this is due to check_option added in view
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.
Suppose we want to hide actual code from which we constructed our view we need to use with encryption clause.
Here Is Sample
CREATE VIEW CourseUpdatableViewWithEncryption
WITH ENCRYPTION
As
select
CourseName,
CourseFee = CourseFee +500
From
dbo.Course
If your user has no access to actual course table but only on ‘CourseUpdatableViewWithEncryption’ he will not able to know that actual CourseFee in table is 500 less than what appears in view.
Now Try To Mess With System.
We have views which have ‘EndDate’ in select list,just drop column from table,try running the select * on any of earlier created view.you will notice that ‘EndDate’ will not appear in result set wherever on ‘EndDate’ was in select list of view but for those view which are using ‘EndDate’ in where condition if any will fail
Error will be
Invalid column name 'EndDate'.
Could not use view or function 'your_view_name’ because of binding errors.
How To Avoid Such Problem?
To avoid such problem we need to use with Schemabinding option,when we use this option we will not be allowed to modify underlying table(s) in a way that it will cause view specified with schemabing to break.so here you will not be allowed to drop column from underlying table which is used in view defined with schemabind which specifies this column in where condition of view creation query.
To be able to drop the column from table that breaks a view with schema binding you have an option of removing schemabinding option from view by altering it,dropping view then you can drop corresponding column in table.
While creating a view with schema binding we need to adhere to condition that all the tables,views or function that we are using in select list of view should be from same database wrt where proposed view will reside.
How to create One
CREATE VIEW CourseUpdatableViewWithSchemabing
WITH SCHEMABINDING
As
select
CourseName,
CourseFees
From
dbo.Course
Where
CourseFees<9000
Now Try Renaming CourseFees to CourseFee as follows
sp_RENAME 'Course.CourseFees', 'CourseFee' , 'COLUMN'
or
sp_RENAME 'Course.CourseName', 'CourseTitle' , 'COLUMN'
you will get an error listed bellow
Object ‘your column name' cannot be renamed because the object participates in enforced dependencies.
Now Try to rename the another column that was not used in view with schemabinding
sp_RENAME 'Course. Deleted', 'IsDeleted' , 'COLUMN'
It runs successfully though gives warning listed bellow
Caution: Changing any part of an object name could break scripts and stored procedures.
Now try to drop a column which is in our schemabinding view’s select list or where condition.
alter table Course drop column CourseFees
or
alter table Course drop column CourseName
The Error will be thrown as follows
The object 'CourseUpdatableViewWithSchemabing' is dependent on column 'yourcolumnname'.
ALTER TABLE DROP COLUMN CourseFees failed because one or more objects access this column.
Every indexed view need to have with schemabinding option in it.
We delve deeper into view for topics like partioned view & indexed view,triggers on view in next Part of article.
No comments:
Post a Comment