Views In SQL Server with examples
This article discusses one of the objects in SQL server – "Views"
A view is a virtual table based on the result-set of an SQL statement.A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
A view is an imaginary table and it contains no data and the tables upon which a view is based are called base table. The advantages of views are as follows
• The y provide table security by restricting access to a predetermined set of rows or columns of a table
• They simplify commands for the user because they allow them to select information from multiple tables.
• They provide data in a different perspective than that of a base table by renaming columns without affecting the base table.
The syntax for creating a view is given below:
CREATE VIEW
Example 1: CREATE VIEW custview (customer, city, representative)
AS
SELECT custid,
city,
repid
FROM customer
WHERE repid = 20
In the above example, the view shows only about representatives whose repid is 20.
We all know how to perform manipulation on table data. The same is applicable to views also. Let us discuss them in detail.
To display the contents of the view, select statement is used.Select * from custview
Similarly we can perform updations, deletions and insertions in a view with certain restrictions. To illustrate this, consider the following views which are derived from the same table item.
Example 2: CREATE VIEW ve1 (item, product)
AS
SELECT item,
product
FROM item
Example 3: CREATE VIEW ve2 (quantity, price)
AS
SELECT quantity,
price
FROM item
We can insert rows into both the views provided other columns in the base table accept null values. If those columns were assigned as not nulls, then we cannot insert rows. We can perform updations or deletions on ve1, because itemid is declared as a primary key which could be used to identify a row. Only after identification, the corresponding rows can be deleted or updated. In the case of ve2, we can perform updation or deletion with dome restrictions. We need to specify which row we actually mean by using the where clause. Joining of tables is also possible in a view. But we cannot update, delete or insert through a view if it selects columns from more than one table. The following example creates a view from two tables.
Example 3: CREATE VIEW joinvew(customername, maximumcredit, total)
AS
SELECT name,
creditlimit,
total
FROM customer,
order_info
WHERE customer.custid = order_info.custid
In the above example the view joinview will contain information about customers and also details about maximum credit that he can avail. Since this information is spread across two tables, we use the concept of join to retrieve them.
Advantages:
1. They are virtual tables and not stored in the database as a distinct object. All that is stored is the SELECT statement.
2. It can be used as a security measure by restricting what the user can see.
3. It can make commonly used complex queries easier to read by encapsulating them into a view. This is a double edged sword though - see disadvantages #3.
Disadvantages:
1. It does not have an optimized execution plan cached so it will not be as fast as a stored procedure.
2. Since it is basically just an abstraction of a SELECT it is marginally slower than doing a pure SELECT.
3. ORDER BY Does Not Work
Nice Explanation, but there is some corrections in your post. I didn't read your total post but as per your post you said that there is some advantages and disadvantages right.? In that you compare View with StoredProcedure, that is not correct. Why because View is different concept and SP is different concept. As you said ORDER By Clause is not possible by using View but that's wrong. we can perform ORDER BY clause by using VIEW's.
Checkout those points whatever I mentioned in above and apart from the above better try to update with all CRUD operations with sample data and output of the query. Then it's being good view to viewers.
Hope you understood...