A view, or virtual table, can be defined as an alternate way of collecting data from one or more tables in a database. Thus, the view consists of rows and columns just like a normal table that is generated dynamically when a query is executed. It, however, does not actually exist as a stored set of data in the database.
Views are useful for the following purposes:
o To restrict a user to specific rows in a table.
o To restrict a user to specific columns.
o To join columns from multiple tables so that they appear to be a single table.
o To get aggregate information instead of supplying details.
Views are generally used to:
Focus on Specific Data
Views allow users to focus only on data that interests them, rather than viewing all of the data in the table. Thus it can be said that views provide a security mechanism for the data in the table.
Simplify Data Manipulation
Views also help in simplifying the data manipulation. Queries, joins, and projections that are used frequently can be defined as views. This helps in keeping the user from having to specify the conditions every time an operation is performed on the data.
Customize Data
Views help users to customize data by allowing them to view the data in different ways. This helps primarily when different users use the data concurrently.
Export and Import Data
Views allow the user to export and import data to and from other applications.
Combine Partitioned Data
We can combine the results of two or more queries by using the T-SQL union operator within a view.
Creating a View
Syntax:
CREATE VIEW view_name [ ( column1, … ) ]
[ WITH ENCRYPTION ]
AS
select_statement
[ WITH CHECK OPTION ]
view_name
The name of the view to be created. It should follow the rules for identifiers.
column1, …
The name to be used for a column in a view. This is necessary only when a column is derived from an arithmetic expression, a function, or a constant, when two or more columns have the same name (caused by a join), or when a column in a view is given a name different from that of the column from which it is derived.
WITH ENCRYPTION
The optional keyword that encrypts the syscomments entries that contain the text of the CREATE VIEW statement.
AS
The keyword that preceds the select_statement parameter.
select_statement
The SELECT statement used to define the view.
WITH CHECK OPTION
Forces all data modification statements executed against the view to adhere to the criteria set within the select_statement. When a row is modified through a view, the WITH CHECK OPTION guarantees that the data remains visible through the view after the modification has been committed.
To create a view, the user must have the appropriate permissions on any tables or views referenced within its definition.
Examples
Code:
CREATE VIEW vwStudentDetails
AS
SELECT * FROM Students;
SELECT Name FROM vwStudentDetails;
Output:
The command(s) completed successfully.
Name
________________________________________
Job Mathew
Rock Feller
Harri Robins
Joe Philip
Nadia Alex
________________________________________
(5 row(s) affected)
Explanation:
The first segment of code creates the view, vwStudentDetails, which contains the Name field. The second code segment executes the view and displays the result set.
Language(s): MS SQL Server
Code:
CREATE VIEW vwStudentDetails_Filter
AS
SELECT Id, Name FROM Students
WHERE Std_Course=3;
SELECT Name FROM vwStudentDetails_Filter;
Output:
The command(s) completed successfully.
Name
________________________________________
Harri Robins
Joe Philip
________________________________________
(2 row(s) affected)
Explanation:
Views can also be used as filters to filter out only the data desired.
The following things are to be considered, while creating a view:
o Views can be created in the current database only.
o View names must adhere to the rules for naming identifiers.
o A view can reference other views.
o DEFAULT definitions, triggers, etc. cannot be associated with views, as it is used in the case of a table.
o Keywords such as ORDER BY, COMPUTE, COMPUTE BY, or INTO cannot be used in the query that defines the view.
o Indexes or full-text index definitions cannot be built on views.
o Views are not created on temporary tables and vice-versa.
o The name of every column in the view must be explicitly specified if:
o Any of the columns in the view is derived from an arithmetic expression, built-in function, or constant.
o Two or more of the columns in the view would otherwise have the same name (usually because the view definition includes a join and the columns from two or more different tables have the same name).
Modifying a View
Syntax:
ALTER VIEW view_name [ ( column1, … ) ]
[ WITH ENCRYPTION ]
AS
select_statement
[ WITH CHECK OPTION ]
A View can be modified by:
1. Deleting the existing view and creating a new one.
2. Using the ALTER VIEW T-SQL command.
Modifying a view does not change any of the dependents on the view, but the dependents are lost when a view is recreated. The parameters from the ALTER VIEW statement are the same as those for the CREATE VIEW statement.
Examples
Code:
ALTER VIEW vwStudentDetails
AS
SELECT * FROM Students
WHERE Std_Grade=1;
SELECT * FROM vwStudentDetails;
Output:
The command(s) completed successfully.
Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
3 Harri Robins 3 78788 1
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(1 row(s) affected)
Explanation:
This example alters the view created earlier, and then executes it.
Renaming a View
Syntax:
sp_rename ‘view1’, ‘view1_new’
view1
Is the name of the view, which is to be renamed.
view1_new
Is the new name of the view.
Views can be renamed. The new name must follow the rules for identifiers.
Examples
Code:
sp_rename ‘vwStudentDetails’, ‘vwStudentDetails1’;
Output:
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to ‘vwStudentDetails1’.
Explanation:
The above example renames the vwStudentDetails to vwStudentDetails1.
Modifying Data Through a View
Syntax:
1. Insert data using a view:
INSERT [ INTO ]
view_name
[ ( column1, … ) ]
VALUES ( value1, … )
2. Update data using a view:
UPDATE view_name
SET column1=value1, …
[ WHERE condition ]
3. Remove Data using a view:
DELETE
[ FROM ]
view_name
[ WHERE condition ]
4. To get information about a view:
sp_helptext view_name
5. Dependencies of a view:
sp_depends view_name
INTO
An optional keyword used between the INSERT keyword and the name of the view.
view_name
The name of the view.
(column1, …)
The list of columns into which values are to be inserted or updated.
VALUES
The keyword used just before the list of values.
(value1, …)
The values that are to be inserted or updated, in the columns mentioned.
SET
The keyword which specifies the list of columns to be updated.
WHERE
The keyword that specifies the conditions that limit the rows to be updated.
condition
The condition that determines which rows are to be updated or deleted.
Views can be used for data modification:
o If the view contains at least one table in the FROM clause of the view definition.
o If no aggregate functions (such as MIN, MAX, AVG, COUNT, SUM, etc.), GROUP BY, UNION, DISTINCT, or TOP clauses are used in the main query. Aggregate functions, however, can be used in a subquery.
o The view has no derived columns (columns derived using operators and functions) in the result set.
Examples
Code:
INSERT INTO vwStudentDetails1(Id, Name, Std_Course, Phone, Std_Grade)
VALUES (20, ‘Sam Simon’, 4, 98758, 2);
Output:
(1 row(s) affected)
Explanation:
Here a new row with Id=20 is inserted into the ‘Students’ table. A SELECT query can verify that the new row was added.
Language(s): MS SQL Server
Code:
UPDATE vwStudentDetails1
SET Name=’Billy James’, Phone=27751
WHERE Id=20;
SELECT * FROM Students WHERE Id=20;
Output:
(1 row(s) affected)
Id Name Std_Course Phone Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
20 Billy James 4 27751 2
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(1 row(s) affected)
Explanation:
In this example, the first statement updates the data, while the second statement is used to verify the results.
Language(s): MS SQL Server
Code:
DELETE FROM vwStudentDetails WHERE Id=20;
Output:
(1 row(s) affected)
Explanation:
Here the row with Id=20 gets deleted from the ‘Students’ table. It can be verified by querying the table.
Language(s): MS SQL Server
Code:
sp_helptext vwStudentDetails;
Output:
CREATE VIEW vwStudentDetails
AS
SELECT * FROM Students;
Explanation:
The above is the definition of the view, vwStudentDetails.
Language(s): MS SQL Server
Code:
sp_depends vwStudentDetails;
Output:
In the current database, the specified object references the following:
name type updated selected column
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
dbo.Students user table no yes Id
dbo.Students user table no yes Name
dbo.Students user table no yes Std_Course
dbo.Students user table no yes Phone
dbo.Students user table no yes Std_Grade
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
Explanation:
The above example displays the dependencies of the view, vwStudentDetails.
Deleting a View
Syntax:
DROP VIEW view_name, …
view_name
The name of the view(s) to be removed.
A view can be permanently removed from a database using the DROP command.
Examples
Code:
DROP VIEW vwStudentDetails;
Output:
The command(s) completed successfully.
Explanation:
The view, vwStudentDetails, gets completely removed from the database.