A few simple questions about SQL Server 'Views'

dbuchanan52

Hello,

I am building an application for Windows Forms using. I am new to SQL
Server 'Views'. Are the following correct understanding of their use?

1.) I believe a view can be referenced in a stored procedure something
like this;
Select * from View1
Is that true?

2.) I believe I can update to a view even when it includes two tables.
I intend to build a dataAdapter to a view for select and update. I
believe it can keep track of how to perform the update? Do I understand
its use correctly?

3.) Is it necessary to include both the the PK and FK when two tables
are part of the view or does it know the relationship established in
the database in the 'diagrams' area take care of that?

Thank you,
dbuchanan

Nov 23 '05 #1

Tom Moreau

1) Yes
2) As long as you update only one of the member tables. That said, some
views are not updateable.
3) No

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

<db*********@hotmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Hello,

I am building an application for Windows Forms using. I am new to SQL
Server 'Views'. Are the following correct understanding of their use?

1.) I believe a view can be referenced in a stored procedure something
like this;
Select * from View1
Is that true?

2.) I believe I can update to a view even when it includes two tables.
I intend to build a dataAdapter to a view for select and update. I
believe it can keep track of how to perform the update? Do I understand
its use correctly?

3.) Is it necessary to include both the the PK and FK when two tables
are part of the view or does it know the relationship established in
the database in the 'diagrams' area take care of that?

Thank you,
dbuchanan

Nov 23 '05 #2

luxspes

db*********@hotmail.com wrote:

Hello,

I am building an application for Windows Forms using. I am new to SQL
Server 'Views'. Are the following correct understanding of their use?

1.) I believe a view can be referenced in a stored procedure something
like this;
Select * from View1
Is that true?
Yes

2.) I believe I can update to a view even when it includes two tables.
I intend to build a dataAdapter to a view for select and update. I
believe it can keep track of how to perform the update? Do I understand
its use correctly?
Copy&Pasted from SQLServer Documentation:

Updatable Views
You can modify the data of an underlying base table through a view, as
long as the following conditions are true:

Any modifications, including UPDATE, INSERT, and DELETE statements, must
reference columns from only one base table.
The columns being modified in the view must directly reference the
underlying data in the table columns. The columns cannot be derived in
any other way, such as through the following:
An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV,
STDEVP, VAR, and VARP.
A computation. The column cannot be computed from an expression that
uses other columns. Columns that are formed by using the set operators
UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a
computation and are also not updatable.
The columns being modified are not affected by GROUP BY, HAVING, or
DISTINCT clauses.
The previous restrictions apply to any subqueries in the FROM clause of
the view, just as they apply to the view itself. Generally, the Database
Engine must be able to unambiguously trace modifications from the view
definition to one base table. For more information, see Modifying Data
Through a View.

If the previous restrictions prevent you from modifying data directly
through a view, consider the following options:

INSTEAD OF Triggers

INSTEAD OF triggers can be created on a view to make a view updatable.
The INSTEAD OF trigger is executed instead of the data modification
statement on which the trigger is defined. This trigger lets the user
specify the set of actions that must happen to process the data
modification statement. Therefore, if an INSTEAD OF trigger exists for a
view on a specific data modification statement (INSERT, UPDATE, or
DELETE), the corresponding view is updatable through that statement. For
more information about INSTEAD OF triggers, see Designing INSTEAD OF
Triggers.
Partitioned Views

If the view is a partitioned view, the view is updatable, subject to
certain restrictions. When it is needed, the Database Engine
distinguishes local partitioned views as the views in which all
participating tables and the view are on the same instance of SQL
Server, and distributed partitioned views as the views in which at least
one of the tables in the view resides on a different or remote server.

For more information about partitioned views, see Creating Partitioned
Views.


3.) Is it necessary to include both the the PK and FK when two tables
are part of the view or does it know the relationship established in
the database in the 'diagrams' area take care of that?
You define Views in SQL... so you have to define the "join" that you
want to use.
Thank you,
dbuchanan

Nov 23 '05 #3