SQL Server Views Tutorial - Create, Alter, Insert, Update, Delete etc..

SQL Server Views
Views allow you to create a virtual representation of table data defined by a SELECT statement.

Views can be used to simplify data access for query writers, obscuring the underlying complexity of the SELECT statement.

Views are also useful for managing security and protecting sensitive data. If you wish to restrict direct table access by the end user, you can grant permissions exclusively to views, rather than to the underlying tables. You can also use views to expose only those columns that you wish the end user to see, including just the necessary columns in the view definition.

Views work as an interface between database tables and user.

You cannot use certain SELECT elements in a view definition, including INTO, OPTION, COMPUTE, COMPUTE BY or ORDER BY. We cannot reference a temporary table.

Views can be used for different purposes:

• To restrict the use of particular columns and/or rows of tables.


• To hide the details of complicated queries. If database applications need queries that involve complicated join operations, the creation of corresponding views can simplify the use of such queries

CREATE VIEW Statement:
A view is created using the CREATE VIEW statement.

/*
create a view for viewing data within the table
Teachers for the user student
*/
CREATE VIEW Student.vu_SelectTeachers
AS
SELECT Name,JobTitle FROM Teacher.Teachers
GO

/*
select data by using the view
*/
SELECT * FROM Student.vu_SelectTeachers
Eg:
/*
create a view for the user teacher
*/
CREATE VIEW Teacher.vu_SelectTeachers
AS
SELECT Id, Name, JobTitle from Teacher.Teachers
GO

/*
select data
*/
SELECT * FROM Teacher.vu_SelectTeachers

Altering and Removing Views
ALTER VIEW statement is used to modify the definition of the view query. The DROP VIEW statement removes the definition of the specified view from the system tables.
Eg:

/*
Altering views
*/
ALTER VIEW Student.vu_SelectTeachers
AS
SELECT Name,JobTitle,Phone from Teacher.Teachers
GO
Eg:
/*
remove a view
*/
DROP VIEW Student.vu_SelectTeachers

INSERT, UPDATE and DELETE statement and VIEW
A view can be used with the INSERT, UPDATE and DELETE statements. When a view is used to insert, update or delete, the rows are actually insert, update or delete into the underlying base table.
The inserting, updating and deleting of rows into the underlying tables is not possible if the corresponding view contains any of the following features:

• The FROM clause in the view definition involves two or more tables and the column list includes columns from more than one table


• A column of the view is derived from an aggregate function


• The SELECT statement in the view contains the GROUP BY clause or the DISTINCT option


• A column of the view is derived from a constant or an expression (except in the case of deletion)

/*
INSERT, UPDATE and DELETE with Views
*/
INSERT INTO Teacher.vu_SelectTeachers(Id, Name,JobTitle) VALUES(4,'jobin','Faculty')

UPDATE Teacher.vu_SelectTeachers SET JobTitle='Trainer' WHERE Id=4

DELETE Teacher.vu_SelectTeachers WHERE Id=4

WITH Encryption:
The WITH ENCRYPTION option encrypts the SELECT statement, thus enhancing the security of the database system. Software vendors who use SQL Server in the back end often encrypt their views or stored procedures in order to prevent tampering or reverse-engineering from clients or competitors. If you use encryption, be sure to save the original, unencrypted definition.

Eg:
/*
creating a view with Encryption
*/
CREATE VIEW Teacher.vu_SelectTeachers
WITH ENCRYPTION
AS
SELECT Id, Name, JobTitle from Teacher.Teachers
GO

WITH SCHEMABINDING
The SCHEMABINDING clause binds the view to the schema of the underlying table. Creating a view with the SCHEMABINDING option locks the tables being referred by the view and prevents any changes that may change the table schema.

Notice two important points while creating a view with SCHEMABINDING OPTION:

• The objects should be referred to by their owner names [two part name].


• SELECT * is not permitted.

/*
create schema with SCHEMABINDING
*/
DROP VIEW Teacher.vu_SelectTeachers

CREATE VIEW Teacher.vu_SelectTeachers
WITH SCHEMABINDING
AS
SELECT Id, Name, JobTitle from Teacher.Teachers
GO

/*
try to change schema of tale Teacher
*/
alter schema student TRANSFER Teacher.Teachers
alter schema teacher TRANSFER student.Teachers

WITH CHECK OPTION
The option WITH CHECK OPTION is used to restrict the insertion of only such rows that satisfy the conditions of the query. If this option is used, Database Engine tests every inserted row to ensure that the conditions in the WHERE clause are evaluated to true.

/*
create schema with CHECK OPTION
*/
DROP VIEW Teacher.vu_SelectTeachers

CREATE VIEW Teacher.vu_SelectTeachers
AS
SELECT Id, Name, JobTitle FROM Teacher.Teachers WHERE JobTitle='faculty'
WITH CHECK OPTION
GO

-- insertion fails on the following query because not faculty
INSERT INTO Teacher.vu_SelectTeachers(Id, Name, JobTitle) VALUES (5,'fff','Manager')
--insertion succeed on the following query because his JobTitle is faculty
INSERT INTO Teacher.vu_SelectTeachers(Id, Name, JobTitle) VALUES (5,'fff','faculty')