Can anyone make it clearer to me?

[milsnips]

Can someone give me a brief but helpful understanding on what a View is, and how it is used, in cooperation with, say, programming a ASP website.

Thanks.
PM

 

[foxdev]

It would be best to start with books online (BOL) and the topic "views, overview". It gives a pretty good overview, including graphics.

Essentially, a view describes how to present your data. A view does not itself contain data, but describes how the data should be rendered when the view is opened.

Think of it like taking a query that you created in Query Analyzer; this query may only have some of the columns of a table, and it may also apply a filter (WHERE clause) to limit the records shown. If you create a view based on that statement, you don't have to remember the column names or the filter criteria if you want the same result - you just SELECT * FROM MYVIEW.

Views also give you the ability to revoke security permissions on the base tables, yet give rights to access the view.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[TomSark]

A view is a virtual table. You issues queries against the view just like you would against a table. The view is defined by a select statement. This select statement is what returns the results back to you when you select from it.

Example: create the view & then select from it using query analyzer

USE PUBS -- connect to pubs
GO

-- create the view
-- Author_id, Author_Last_Name, Author_First_Name are names you define (optional) that get returned from the view
CREATE VIEW myView ( Author_id, Author_Last_Name, Author_First_Name )
AS
SELECT au_id, -- this defines the actual columns you
au_lname, -- are selecting from
au_fname
FROM authors
GO

-- select from your view
SELECT Author_id,
Author_Last_Name,
Author_First_Name
FROM myView -- just like from a table
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[cdukes]

A view is a select query that is held on the server. They are used to focus, simplify, and customize each user’s perception of the database. They can also be used as a security mechanism for giving users persmissions to view only ceratin elements of data in tables rather than all data.

They can also be used to simplify the data the end user sees.

Unlike stored procedures, views can be used in joins, where clauses etc.

As an example, supposing we have a personnel table with the firstName, surname and bank details of all personnel. If you create a view which only selects the FirstName and LastName, The bank details are not shown, Thus you can put select permissions on the view, but NOT the table.

CREATE VIEW vuPersonnel AS
SELECT FirstName, LastName from tblPersonnel

As another example, supposing we have a complex method for storing data such as a integer field that is split as a bit field to store the status of an order. The order can be Sent, Invoiced, Received etc.

The user will not understand that a value of 11 means that the order has been Sent, invoiced and Acknowledged so we can provide a view that we can join on to give the current status of the order eg.

Create View vuOrderState
AS

SELECT OrderId,
(CASE WHEN (MyFlag &amp; 0x0001) <> 0 THEN 1 ELSE 0 END) Sent,
(CASE WHEN (MyFlag &amp; 0x0002) <> 0 THEN 1 ELSE 0 END) Invoiced,
(CASE WHEN (MyFlag &amp; 0x0080) <> 0 THEN 1 ELSE 0 END) received,
(CASE WHEN (MyFlag &amp; 0x0004) <> 0 THEN 1 ELSE 0 END) Acknowledged
.................
FROM tblOrders

An end user Query could look like this (excuse the * )

Select * from tblorders OR
Inner JOIN vuOrderState VOR
ON VOR.OrderId = OR.OrderID
Where VOR.Recevived = 1
and OR.Invoiced = 1


Hope this is usefull and makes sense,


Chris Dukes