Views vs Queries

**John Bell** · Jul 20 '05, 02:01 AM

Re: Views vs Queries

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." <BadDog@thepoun d.com> wrote in message
news:eScrb.5772 $HE1.639@newssv r25.news.prodig y.com...[color=blue]
> 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[/color]
relationships[color=blue]
> between various tables, and to get the information that I need into the[/color]
user[color=blue]
> 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[/color]
into[color=blue]
> a single table as a view, it seems to me that I've completed the first[/color]
step[color=blue]
> at the database level, thus making my queries or stored procedures easier[/color]
to[color=blue]
> implement.
>
> I would appreciate any comments which can help me to decide when it is or[/color]
is[color=blue]
> 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.
>
>
>[/color]

**Mike N.** · Jul 20 '05, 02:02 AM

Re: Views vs Queries

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" <jbellnewsposts @hotmail.com> wrote in message
news:bojpc2$jrv $1@titan.btinte rnet.com...[color=blue]
> 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[/color]
actions[color=blue]
> can be maintained and controlled easily.
>
> Therefore a combination of views used in stored procedures is probably the
> solution.
>
> John
>
>
>
>
>
>
> "Mike N." <BadDog@thepoun d.com> wrote in message
> news:eScrb.5772 $HE1.639@newssv r25.news.prodig y.com...[color=green]
> > 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[/color][/color]
I[color=blue][color=green]
> > can't see the downside to doing so.
> >
> > I have a rather complicated database structure in terms of the[/color]
> relationships[color=green]
> > between various tables, and to get the information that I need into the[/color]
> user[color=green]
> > interface forms I have to use a number of nested queries (first to[/color][/color]
combine[color=blue][color=green]
> > 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[/color][/color]
some[color=blue][color=green]
> > cases I would need a 3 level deep nested query to get to what I need.[/color][/color]
By[color=blue][color=green]
> > implementing the first step, that of combing the normalized tables back[/color]
> into[color=green]
> > a single table as a view, it seems to me that I've completed the first[/color]
> step[color=green]
> > at the database level, thus making my queries or stored procedures[/color][/color]
easier[color=blue]
> to[color=green]
> > implement.
> >
> > I would appreciate any comments which can help me to decide when it is[/color][/color]
or[color=blue]
> is[color=green]
> > not appropriate to use a view in these circumstances. I should point[/color][/color]
out[color=blue][color=green]
> > that I don't plan on using the views as part of action queries since[/color][/color]
they[color=blue][color=green]
> > are made up of multiple tables, I'm primarily interested in using them[/color][/color]
to[color=blue][color=green]
> > output data which has been consiolidated from multiple tables.
> >
> > Thanks in advance to any and all who take the time to reply.
> >
> >
> >[/color]
>
>[/color]

**Ryan** · Jul 20 '05, 02:02 AM

Re: Views vs Queries

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." <BadDog@thepoun d.com> wrote in message news:<icerb.578 5$542.1165@news svr25.news.prod igy.com>...[color=blue]
> 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" <jbellnewsposts @hotmail.com> wrote in message
> news:bojpc2$jrv $1@titan.btinte rnet.com...[color=green]
> > 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[/color]
> actions[color=green]
> > can be maintained and controlled easily.
> >
> > Therefore a combination of views used in stored procedures is probably the
> > solution.
> >
> > John
> >
> >
> >
> >
> >
> >
> > "Mike N." <BadDog@thepoun d.com> wrote in message
> > news:eScrb.5772 $HE1.639@newssv r25.news.prodig y.com...[color=darkred]
> > > 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[/color][/color]
> I[color=green][color=darkred]
> > > can't see the downside to doing so.
> > >
> > > I have a rather complicated database structure in terms of the[/color][/color]
> relationships[color=green][color=darkred]
> > > between various tables, and to get the information that I need into the[/color][/color]
> user[color=green][color=darkred]
> > > interface forms I have to use a number of nested queries (first to[/color][/color]
> combine[color=green][color=darkred]
> > > 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[/color][/color]
> some[color=green][color=darkred]
> > > cases I would need a 3 level deep nested query to get to what I need.[/color][/color]
> By[color=green][color=darkred]
> > > implementing the first step, that of combing the normalized tables back[/color][/color]
> into[color=green][color=darkred]
> > > a single table as a view, it seems to me that I've completed the first[/color][/color]
> step[color=green][color=darkred]
> > > at the database level, thus making my queries or stored procedures[/color][/color]
> easier
> to[color=green][color=darkred]
> > > implement.
> > >
> > > I would appreciate any comments which can help me to decide when it is[/color][/color]
> or
> is[color=green][color=darkred]
> > > not appropriate to use a view in these circumstances. I should point[/color][/color]
> out[color=green][color=darkred]
> > > that I don't plan on using the views as part of action queries since[/color][/color]
> they[color=green][color=darkred]
> > > are made up of multiple tables, I'm primarily interested in using them[/color][/color]
> to[color=green][color=darkred]
> > > output data which has been consiolidated from multiple tables.
> > >
> > > Thanks in advance to any and all who take the time to reply.
> > >
> > >
> > >[/color]
> >
> >[/color][/color]

**Mike N.** · Jul 20 '05, 02:08 AM

Re: Views vs Queries

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

"Ryan" <ryanofford@hot mail.com> wrote in message
news:7802b79d.0 311110045.3c241 ffb@posting.goo gle.com...[color=blue]
> 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." <BadDog@thepoun d.com> wrote in message[/color]
news:<icerb.578 5$542.1165@news svr25.news.prod igy.com>...[color=blue][color=green]
> > 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[/color][/color]
tables[color=blue][color=green]
> > are, i.e. as data suppliers for windows forms objects. This sounds like[/color][/color]
what[color=blue][color=green]
> > 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[/color][/color]
that[color=blue][color=green]
> > option (this is an ameteur type project on my home system). Will that[/color][/color]
make[color=blue][color=green]
> > a difference between choosing a query over a view?
> >
> > Once again, thank you for replying to my question (I have lots more too[/color][/color]
:)[color=blue][color=green]
> >
> > Mike
> >
> >
> > "John Bell" <jbellnewsposts @hotmail.com> wrote in message
> > news:bojpc2$jrv $1@titan.btinte rnet.com...[color=darkred]
> > > Hi
> > >
> > > Views are a way to encapsulate code and abstract the user from some of[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > complexity of the underlying structure, as you state. Whether you use[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > view, stored procedure, function or query will depend on the task in[/color][/color][/color]
hand.[color=blue][color=green][color=darkred]
> > >
> > > Advantages of views include reduced maintainance and the ability to be
> > > indexed. The biggest dis-advantage is security in that you have access[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > 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[/color]
> > actions[color=darkred]
> > > can be maintained and controlled easily.
> > >
> > > Therefore a combination of views used in stored procedures is probably[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > solution.
> > >
> > > John
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Mike N." <BadDog@thepoun d.com> wrote in message
> > > news:eScrb.5772 $HE1.639@newssv r25.news.prodig y.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.[/color][/color][/color]
I'm a[color=blue][color=green][color=darkred]
> > > > little confused as to the fundamental differences between a view and[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > > query, or rather, when it is more appropriate to use one vs. the[/color][/color][/color]
other.[color=blue][color=green][color=darkred]
> > > >
> > > > It seems to me that most select queries can be implemented as views,[/color][/color][/color]
and[color=blue][color=green]
> > I[color=darkred]
> > > > can't see the downside to doing so.
> > > >
> > > > I have a rather complicated database structure in terms of the[/color]
> > relationships[color=darkred]
> > > > between various tables, and to get the information that I need into[/color][/color][/color]
the[color=blue][color=green]
> > user[color=darkred]
> > > > interface forms I have to use a number of nested queries (first to[/color]
> > combine[color=darkred]
> > > > tables together that have been normalized to place the related info[/color][/color][/color]
in a[color=blue][color=green][color=darkred]
> > > > single table, then next to extract only the info that I require. In[/color]
> > some[color=darkred]
> > > > cases I would need a 3 level deep nested query to get to what I[/color][/color][/color]
need.[color=blue][color=green]
> > By[color=darkred]
> > > > implementing the first step, that of combing the normalized tables[/color][/color][/color]
back[color=blue][color=green]
> > into[color=darkred]
> > > > a single table as a view, it seems to me that I've completed the[/color][/color][/color]
first[color=blue][color=green]
> > step[color=darkred]
> > > > at the database level, thus making my queries or stored procedures[/color]
> > easier
> > to[color=darkred]
> > > > implement.
> > > >
> > > > I would appreciate any comments which can help me to decide when it[/color][/color][/color]
is[color=blue][color=green]
> > or
> > is[color=darkred]
> > > > not appropriate to use a view in these circumstances. I should[/color][/color][/color]
point[color=blue][color=green]
> > out[color=darkred]
> > > > that I don't plan on using the views as part of action queries since[/color]
> > they[color=darkred]
> > > > are made up of multiple tables, I'm primarily interested in using[/color][/color][/color]
them[color=blue][color=green]
> > to[color=darkred]
> > > > output data which has been consiolidated from multiple tables.
> > > >
> > > > Thanks in advance to any and all who take the time to reply.
> > > >
> > > >
> > > >
> > >
> > >[/color][/color][/color]

**Stephen Hendricks** · Jul 20 '05, 02:08 AM

Re: Views vs Queries

>>>>>
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** · Jul 20 '05, 02:09 AM

Re: Views vs Queries

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/default.asp?url=/library/en-us/tsqlref/ts_create2_30hj.asp

John

Stephen Hendricks <happy@londonfg .com> wrote in message news:<3fbe81a2$ 0$198$75868355@ news.frii.net>. ..[color=blue][color=green][color=darkred]
> >>>>>[/color][/color]
> 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![/color]

**Stephen Hendricks** · Jul 20 '05, 02:09 AM

Re: Views vs Queries

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 (PersonnelPubli c) 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** · Jul 20 '05, 02:10 AM

Re: Views vs Queries

A full description of ownership chains is given at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_4iyb.asp

John

Stephen Hendricks <happy@londonfg .com> wrote in message news:<3fc2530c$ 0$197$75868355@ news.frii.net>. ..[color=blue]
> 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 (PersonnelPubli c) 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![/color]

Views vs Queries

Views vs Queries

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment