Search content within the blog

Friday, April 17, 2009

Views in SqlServer

A VIEW is a virtual table based on the result-set of a SELECT statement. A view contains rows and columns like a real table. The fields in a view consist fields from one or more real tables in the database.

Syntax:

Views may be considered as virtual tables. Generalize way a table has a set of definition, and it physically stores the data. A view also has a set of definitions, which is build on top of table(s) or other view(s), and it does not physically store the data.

CREATE VIEW "VIEW_NAME" AS "SQL Statement"

Example:

TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

and we want to create a view called V_Customer that contains only the First_Name, Last_Name, and Country columns from this table, we would type in,

CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer

Now we have a view called V_Customer with the following structure:

View V_Customer
(First_Name char(50),
Last_Name char(50),
Country char(25))


Apply View on Joins

To joins two or more tables view can be used. In this case, users only see one view rather than two tables, and the SQL statement users need to issue becomes much simpler. Let's say we have the following two tables:
Table:Store_Information
Store_name Sales Date
Los Angeles $1500 Jan-05-2005
San Diego $250 Jan-07-2005
Los Angeles $300 Jan-08-2005
Boston $700 Jan-08-2005

Table: Geography
region_name store_name
East Boston
East New York
West Los Angeles
West San Diego

If we want to build a view that has sales by region information. We would issue the following SQL statement:

CREATE VIEW V_REGION_SALES
AS SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name

It gives a view called V_REGION_SALES, that has been defined to store sales by region records. If we want to find out the content of this view then following query:

SELECT * FROM V_REGION_SALES

Output:
REGION SALES
East $700
West $2050

No comments:

Post a Comment