SQL Server - Details about View - Asked By aman on 28-May-11 03:18 AM

hi all,
what is view and why we use view with example.

how to generate view on multiple table and update it with some example.

In which scenario to use view

thanks,
regards,
Aman khan
Ravi S replied to aman on 28-May-11 03:24 AM
HI

A view is, in essence, a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables.

Creating a VIEW

The syntax for creating a VIEW is:

CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;


For example:

CREATE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'IBM';

This would create a virtual table based on the result set of the select statement. You can now query the view as follows:

SELECT *
FROM sup_orders;


Updating a VIEW

You can update a VIEW without dropping it by using the following syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;


For example:

CREATE or REPLACE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'Microsoft';


Dropping a VIEW

The syntax for dropping a VIEW is:

DROP VIEW view_name;

For example:

DROP VIEW sup_orders;



refer the links also
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzatc%2Frzatcviewmultsql.htm
http://msdn.microsoft.com/en-us/library/f5scy1hs(v=vs.80).aspx
Riley K replied to aman on 28-May-11 03:24 AM
In SQL Server a view represents a virtual table. Just like a real table, a view consists of rows with columns, and you can retrieve data from a view (sometimes even update data in a view). The fields in the view’s virtual table are the fields of one or more real tables in the database. You can use views to join two tables in your database and present the underlying data as if the data were coming from a single table, thus simplifying the schema of your database for users performing ad-hoc reporting.

Sample View

The sample database Northwind in SQL Server has a number of views installed by default. One example is the “Current Product List” view, shown here.

 
SELECT
   Product_List.ProductID, Product_List.ProductName
 FROM
   Products AS Product_List
 WHERE (Product_List.Discontinued = 0)

From inside an application we can issue the following SQL query to retrieve a set of records representing active products.

SELECT ProductID, ProductName from [Current Product List]

Now with the above view is created...

When to Use..

You need to have a goal in mind when creating a view. There are a number of scenarios where you will want to look for a view as a solution.
  • To hide the complexity of the underlying database schema, or customize the data and schema for a set of users.
  • To control access to rows and columns of data.
  • To aggregate data for performance.

Jitendra Faye replied to aman on 28-May-11 03:25 AM

In SQL, 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.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

syntax-

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

VIEW for several reasons-

A) A well constructed view does tend to perform faster than a query, though with query optimization you may not notice much of a difference.


B)
It keeps knowledge of the database structure within the database itself - adding a good layer of abstraction (as a side note, consider using a stored procedure rather than an inline query - this also keeps database knowledge within the database itself)


C)
If you do need to make a structural change to the database, you can keep the view consistent without needing to rebuild your code.

Anoop S replied to aman on 28-May-11 03:25 AM

In SQL, 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.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Views offer the following advantages:

1. Ease of use: A view hides the complexity of the database tables from end users. Essentially we can think of views as a layer of abstraction on top of the database tables.

2. Space savings: Views takes very little space to store, since they do not store actual data.

3. Additional data security: Views can include only certain columns in the table so that only the non-sensitive columns are included and exposed to the end user. In addition, some databases allow views to have different security settings, thus hiding sensitive data from prying eyes
Jitendra Faye replied to aman on 28-May-11 03:28 AM

Can You Create a View with Data from Multiple Tables? The answer is yes. A view can be created with a SELECT statement to join data from multiple tables.

It is a common practice to normalize data into multiple tables. Then using a view to de-normalize them into a single output.

The tutorial exercise below shows you how to create a view to normalize data from two tables SalesOrderHeader and Customer in the sample database AdventureWorksLT.

USE AdventureWorksLT;
GO

CREATE VIEW SalesOrderView AS
   SELECT o.SalesOrderNumber, o.OrderDate, o.TotalDue,
      c.FirstName, c.LastName, c.CompanyName
   FROM SalesLT.SalesOrderHeader o, SalesLT.Customer c
   WHERE o.CustomerID = c.CustomerID
GO

SELECT TOP 10 SalesOrderNumber, TotalDue, CompanyName 
   FROM SalesOrderView;
GO






SalesOrderNumber TotalDue CompanyName ---------------- ----------- ------------------------------ SO71915 2361.6403 Aerobic Exercise Company SO71938 98138.2131 Bulk Discount Store SO71783 92663.5609 Eastside Department Store SO71899 2669.3183 Coalition Bike Company SO71898 70698.9922 Instruments and Parts Company SO71902 81834.9826 Many Bikes Store SO71832 39531.6085 Closest Bicycle Store SO71776 87.0851 West Side Mart SO71797 86222.8072 Riding Cycles SO71895 272.6468 Futuristic Bikes
Reena Jain replied to aman on 28-May-11 03:36 AM
Hi,

The view is a virtual table, which can have the multiple columns from the one or more table. It can be used like the normal table. Normally view cannot store the data permanently in the table. When we create the view it stores the view definition schema as object under the concern database.


Let us see the syntax of the create view

CREATE VIEW View Name [Alias name1, name2,]
  
AS
  
SELECT * from [Tables]

The create view can be created with the view name and the alias can be given in the view name parameter parenthesis. The view schema can be stored in the encrypted format. Here is an option like SCHEMA BINDING; this is an important mile stone in the view to allow the developers to create the permanent view.

When to use VIEW?

When you have complex queries, that use many places in the stored procedures or functions, etc..,

It will be used as security mechanism in the web applications. When we use the original table in the web applications the hackers may drop the table. That time the original data will be persist in the table.

Hope this will help you

When you want to hide the particular columns to the specific people then we can create the specialized view.

aman replied to Jitendra Faye on 28-May-11 04:52 AM
hi there,

is view is updateable when we create view using inner join from two tables.
and at the same time the table is also updated or not.

thanks,
regards,
aman khan
Jitendra Faye replied to aman on 28-May-11 05:46 AM

A view is not updatable if any of the following conditions are true:

  • the keyword DISTINCT is used in the view definition
  • the select list contains components other than column specifications, or contains more than one specification of the same column
  • the FROM clause specifies more than one table reference or refers to a non-updatable view
  • the GROUP BY clause is used in the view definition
  • the HAVING clause is used in the view definition
Note: By defining an INSTEAD OF trigger any view can be made updatable. If all the INSTEAD OF triggers on the view are dropped, the view will revert to not updatable if one or more of the conditions are true.