Views vs Queries

[Mike N.]

Hello:

I am new to T-SQL programing, and relativly new to SQL statements in
general, although I have a good understanding of database theory. I'm a
little confused as to the fundamental differences between a view and a
query, or rather, when it is more appropriate to use one vs. the other.

It seems to me that most select queries can be implemented as views, and I
can't see the downside to doing so.

I have a rather complicated database structure in terms of the relationships
between various tables, and to get the information that I need into the user
interface forms I have to use a number of nested queries (first to combine
tables together that have been normalized to place the related info in a
single table, then next to extract only the info that I require. In some
cases I would need a 3 level deep nested query to get to what I need. By
implementing the first step, that of combing the normalized tables back into
a single table as a view, it seems to me that I've completed the first step
at the database level, thus making my queries or stored procedures easier to
implement.

I would appreciate any comments which can help me to decide when it is or is
not appropriate to use a view in these circumstances. I should point out
that I don't plan on using the views as part of action queries since they
are made up of multiple tables, I'm primarily interested in using them to
output data which has been consiolidated from multiple tables.

Thanks in advance to any and all who take the time to reply.

[John Bell]

Hi

Views are a way to encapsulate code and abstract the user from some of the
complexity of the underlying structure, as you state. Whether you use a
view, stored procedure, function or query will depend on the task in hand.

Advantages of views include reduced maintainance and the ability to be
indexed. The biggest dis-advantage is security in that you have access to
base tables.

The most common security model dictates that code is written in stored
procedure therefore direct access to base tables can be denied, and actions
can be maintained and controlled easily.

Therefore a combination of views used in stored procedures is probably the
solution.

John


"Mike N." <Ba****@thepound.com> wrote in message
news:eS****************@newssvr25.news.prodigy.com ...

Hello:

I am new to T-SQL programing, and relativly new to SQL statements in
general, although I have a good understanding of database theory. I'm a
little confused as to the fundamental differences between a view and a
query, or rather, when it is more appropriate to use one vs. the other.

It seems to me that most select queries can be implemented as views, and I
can't see the downside to doing so.

I have a rather complicated database structure in terms of the relationships between various tables, and to get the information that I need into the user interface forms I have to use a number of nested queries (first to combine
tables together that have been normalized to place the related info in a
single table, then next to extract only the info that I require. In some
cases I would need a 3 level deep nested query to get to what I need. By
implementing the first step, that of combing the normalized tables back into a single table as a view, it seems to me that I've completed the first step at the database level, thus making my queries or stored procedures easier to implement.

I would appreciate any comments which can help me to decide when it is or is not appropriate to use a view in these circumstances. I should point out
that I don't plan on using the views as part of action queries since they
are made up of multiple tables, I'm primarily interested in using them to
output data which has been consiolidated from multiple tables.

Thanks in advance to any and all who take the time to reply.

[Mike N.]

Thanks you for taking the time to reply. I don't intend to have direct
access to any views, they will all be used in the same way the base tables
are, i.e. as data suppliers for windows forms objects. This sounds like what
you are saying, i.e. I establish the view, and then use it from a stored
procedure to send the data in it back to the middle tier.

Part of my concern had to do with the updating of views since they don't
automatically update with the base tables, but this seems to be the same
issue as data in a query.

FYI, I can't index my views as my version of SQL-Server doesn't include that
option (this is an ameteur type project on my home system). Will that make
a difference between choosing a query over a view?

Once again, thank you for replying to my question (I have lots more too :)

Mike
"John Bell" <jb************@hotmail.com> wrote in message
news:bo**********@titan.btinternet.com...

Hi

Views are a way to encapsulate code and abstract the user from some of the
complexity of the underlying structure, as you state. Whether you use a
view, stored procedure, function or query will depend on the task in hand.

Advantages of views include reduced maintainance and the ability to be
indexed. The biggest dis-advantage is security in that you have access to
base tables.

The most common security model dictates that code is written in stored
procedure therefore direct access to base tables can be denied, and actions can be maintained and controlled easily.

Therefore a combination of views used in stored procedures is probably the
solution.

John


"Mike N." <Ba****@thepound.com> wrote in message
news:eS****************@newssvr25.news.prodigy.com ...

Hello:

I am new to T-SQL programing, and relativly new to SQL statements in
general, although I have a good understanding of database theory. I'm a
little confused as to the fundamental differences between a view and a
query, or rather, when it is more appropriate to use one vs. the other.

It seems to me that most select queries can be implemented as views, and I can't see the downside to doing so.

I have a rather complicated database structure in terms of the relationships

between various tables, and to get the information that I need into the

user

interface forms I have to use a number of nested queries (first to combine tables together that have been normalized to place the related info in a
single table, then next to extract only the info that I require. In some cases I would need a 3 level deep nested query to get to what I need. By implementing the first step, that of combing the normalized tables back

into

a single table as a view, it seems to me that I've completed the first

step

at the database level, thus making my queries or stored procedures easier to

implement.

I would appreciate any comments which can help me to decide when it is

or is

not appropriate to use a view in these circumstances. I should point

out that I don't plan on using the views as part of action queries since they are made up of multiple tables, I'm primarily interested in using them to output data which has been consiolidated from multiple tables.

Thanks in advance to any and all who take the time to reply.

[Ryan]

As a general rule of thumb, I use queries where I need to obtain
something particularily complex (can be several hundred lines) and
have to use temporary tables (something you can't do in a view). We
often build up a series of statements into a final result this way. We
use views for more simplistic ways of getting the data (although still
quite complex), where it is possible to fully understand the entire
SQL statement and can obtain the data in a single statement. You can
always use a view into another view and SQL will treat it as a single
statement. It's a very broad explanation at best.

Don't forget you can use UNION ALL in views (and queries) to bring two
identical sets of results together.

Using a view does give you the advantage that you can modify the view
/ results to some extent without changing the application and
re-releasing it.

You can't update the data back in the tables through a view. For
exaple, an UPDATE statement will work in a query, but not in a view.
The other option for this is to use a stored procedure.

One thing worth having a look at is the execution plan of a query /
view. SQL determines the best method of obtaining the data and
compiles an execution plan which it caches. By working with your query
/ view and optimising it, you can have an affect on this and speed up
the query. It may reduce table scans which take up valuable time and
resource.

All of this depends on what it is you want to do with your data.

"Mike N." <Ba****@thepound.com> wrote in message news:<ic*****************@newssvr25.news.prodigy.c om>...

Thanks you for taking the time to reply. I don't intend to have direct
access to any views, they will all be used in the same way the base tables
are, i.e. as data suppliers for windows forms objects. This sounds like what
you are saying, i.e. I establish the view, and then use it from a stored
procedure to send the data in it back to the middle tier.

Part of my concern had to do with the updating of views since they don't
automatically update with the base tables, but this seems to be the same
issue as data in a query.

FYI, I can't index my views as my version of SQL-Server doesn't include that
option (this is an ameteur type project on my home system). Will that make
a difference between choosing a query over a view?

Once again, thank you for replying to my question (I have lots more too :)

Mike
"John Bell" <jb************@hotmail.com> wrote in message
news:bo**********@titan.btinternet.com...

Hi

Views are a way to encapsulate code and abstract the user from some of the
complexity of the underlying structure, as you state. Whether you use a
view, stored procedure, function or query will depend on the task in hand.

Advantages of views include reduced maintainance and the ability to be
indexed. The biggest dis-advantage is security in that you have access to
base tables.

The most common security model dictates that code is written in stored
procedure therefore direct access to base tables can be denied, and

actions

can be maintained and controlled easily.

Therefore a combination of views used in stored procedures is probably the
solution.

John


"Mike N." <Ba****@thepound.com> wrote in message
news:eS****************@newssvr25.news.prodigy.com ...

Hello:

I am new to T-SQL programing, and relativly new to SQL statements in
general, although I have a good understanding of database theory. I'm a
little confused as to the fundamental differences between a view and a
query, or rather, when it is more appropriate to use one vs. the other.

It seems to me that most select queries can be implemented as views, and I can't see the downside to doing so.

I have a rather complicated database structure in terms of the relationships between various tables, and to get the information that I need into the user interface forms I have to use a number of nested queries (first to combine tables together that have been normalized to place the related info in a
single table, then next to extract only the info that I require. In some cases I would need a 3 level deep nested query to get to what I need. By implementing the first step, that of combing the normalized tables back into a single table as a view, it seems to me that I've completed the first step at the database level, thus making my queries or stored procedures easier
to implement.

I would appreciate any comments which can help me to decide when it is or
is not appropriate to use a view in these circumstances. I should point out that I don't plan on using the views as part of action queries since they are made up of multiple tables, I'm primarily interested in using them to output data which has been consiolidated from multiple tables.

Thanks in advance to any and all who take the time to reply.

[Mike N.]

Thank you for your reply Ryan, your comments have been helpful to me.

"Ryan" <ry********@hotmail.com> wrote in message
news:78**************************@posting.google.c om...

As a general rule of thumb, I use queries where I need to obtain
something particularily complex (can be several hundred lines) and
have to use temporary tables (something you can't do in a view). We
often build up a series of statements into a final result this way. We
use views for more simplistic ways of getting the data (although still
quite complex), where it is possible to fully understand the entire
SQL statement and can obtain the data in a single statement. You can
always use a view into another view and SQL will treat it as a single
statement. It's a very broad explanation at best.

Don't forget you can use UNION ALL in views (and queries) to bring two
identical sets of results together.

Using a view does give you the advantage that you can modify the view
/ results to some extent without changing the application and
re-releasing it.

You can't update the data back in the tables through a view. For
exaple, an UPDATE statement will work in a query, but not in a view.
The other option for this is to use a stored procedure.

One thing worth having a look at is the execution plan of a query /
view. SQL determines the best method of obtaining the data and
compiles an execution plan which it caches. By working with your query
/ view and optimising it, you can have an affect on this and speed up
the query. It may reduce table scans which take up valuable time and
resource.

All of this depends on what it is you want to do with your data.

"Mike N." <Ba****@thepound.com> wrote in message

news:<ic*****************@newssvr25.news.prodigy.c om>...

Thanks you for taking the time to reply. I don't intend to have direct
access to any views, they will all be used in the same way the base tables are, i.e. as data suppliers for windows forms objects. This sounds like what you are saying, i.e. I establish the view, and then use it from a stored
procedure to send the data in it back to the middle tier.

Part of my concern had to do with the updating of views since they don't
automatically update with the base tables, but this seems to be the same
issue as data in a query.

FYI, I can't index my views as my version of SQL-Server doesn't include that option (this is an ameteur type project on my home system). Will that make a difference between choosing a query over a view?

Once again, thank you for replying to my question (I have lots more too :)
Mike
"John Bell" <jb************@hotmail.com> wrote in message
news:bo**********@titan.btinternet.com...

Hi

Views are a way to encapsulate code and abstract the user from some of the complexity of the underlying structure, as you state. Whether you use a view, stored procedure, function or query will depend on the task in hand.
Advantages of views include reduced maintainance and the ability to be
indexed. The biggest dis-advantage is security in that you have access to base tables.

The most common security model dictates that code is written in stored
procedure therefore direct access to base tables can be denied, and

actions

can be maintained and controlled easily.

Therefore a combination of views used in stored procedures is probably the solution.

John


"Mike N." <Ba****@thepound.com> wrote in message
news:eS****************@newssvr25.news.prodigy.com ...
> Hello:
>
> I am new to T-SQL programing, and relativly new to SQL statements in
> general, although I have a good understanding of database theory. I'm a > little confused as to the fundamental differences between a view and a > query, or rather, when it is more appropriate to use one vs. the other. >
> It seems to me that most select queries can be implemented as views, and

I

> can't see the downside to doing so.
>
> I have a rather complicated database structure in terms of the

relationships

> between various tables, and to get the information that I need into

the user

> interface forms I have to use a number of nested queries (first to

combine

> tables together that have been normalized to place the related info

in a > single table, then next to extract only the info that I require. In

some

> cases I would need a 3 level deep nested query to get to what I

need. By

> implementing the first step, that of combing the normalized tables

back into

> a single table as a view, it seems to me that I've completed the

first step

> at the database level, thus making my queries or stored procedures

easier
to

> implement.
>
> I would appreciate any comments which can help me to decide when it

is or
is

> not appropriate to use a view in these circumstances. I should

point out

> that I don't plan on using the views as part of action queries since

they

> are made up of multiple tables, I'm primarily interested in using

them to

> output data which has been consiolidated from multiple tables.
>
> Thanks in advance to any and all who take the time to reply.
>
>
>

[Stephen Hendricks]

>>>>>
The biggest dis-advantage [of views] is security in that you have access
to base tables.
<<<<<

I don't believe that you need to grant the user access to the base table
in order to have the view be able to access the base table. As long as
the owner of the view has the appropriate rights granted, the chain of
ownership should handle the rest.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[John Bell]

See books online:

To execute the view, the user must have SELECT permission on the
tables, views, and table-valued functions being referenced in the
view, and EXECUTE permission on the scalar-valued functions being
invoked in the view.

http://msdn.microsoft.com/library/de...eate2_30hj.asp

John

Stephen Hendricks <ha***@londonfg.com> wrote in message news:<3f*********************@news.frii.net>...

>>

The biggest dis-advantage [of views] is security in that you have access
to base tables.
<<<<<

I don't believe that you need to grant the user access to the base table
in order to have the view be able to access the base table. As long as
the owner of the view has the appropriate rights granted, the chain of
ownership should handle the rest.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Stephen Hendricks]

In order to access any database object you need the proper permissions.
This does NOT mean that the user needs to have these permissions
directly; they can get the permission through the chain of ownership.
In this case, the owner of the view (dbo?) would have the select
permissions and the user would be granted permissions to the view; not
the table.

One of the uses of a view is to limit access to sensitive data in a
table. For instance, a Personnel table that had both public (Name,
Title, Phone Extension, etc.) and private (Salary, Medical, etc.)

A view would be created (PersonnelPublic) that only selected the public
data. Select rights to this view would be granted to the employees.

Now if an employee selects on the view PersonnelPublic, they get the
data returned but if they select on the base table Personnel, they get a
permission denied error.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[John Bell]

A full description of ownership chains is given at:

http://msdn.microsoft.com/library/de...urity_4iyb.asp

John

Stephen Hendricks <ha***@londonfg.com> wrote in message news:<3f*********************@news.frii.net>...

In order to access any database object you need the proper permissions.
This does NOT mean that the user needs to have these permissions
directly; they can get the permission through the chain of ownership.
In this case, the owner of the view (dbo?) would have the select
permissions and the user would be granted permissions to the view; not
the table.

One of the uses of a view is to limit access to sensitive data in a
table. For instance, a Personnel table that had both public (Name,
Title, Phone Extension, etc.) and private (Salary, Medical, etc.)

A view would be created (PersonnelPublic) that only selected the public
data. Select rights to this view would be granted to the employees.

Now if an employee selects on the view PersonnelPublic, they get the
data returned but if they select on the base table Personnel, they get a
permission denied error.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!