Views or WHERE -- sql server programming

Greg Linwood
7/17/2003 8:33:48 PM

Hi Ray.

As you've already indicated, the problem you'll face with
using views for this is that you can't pass a parameter to
a view, so the WHERE clause would need to be hard-wired to
a specific UserID.

A table valued, user defined function might be better for
the task, as it can take a parameter & work within a
select (as a view can).

eg:

use tempdb
go
set nocount on
go
create table users (
userid int primary key
, username varchar(200)
)
go
create table links (
linkid int primary key
, userid int foreign key references users (userid)
, linkurl varchar(7000)
)
go
create table addressinfo (
userid int
, addresstype char(2) not null check (addresstype in
('hm', 'wk'))
, address varchar(7000)
, constraint pk_addressinfo primary key (userid,
addresstype)
)
go
insert into users (userid, username) values (1, 'bill')
insert into users (userid, username) values (2, 'bob')
go
insert into links (linkid, userid, linkurl) values (1,
1, 'http://www.microsoft.com')
insert into links (linkid, userid, linkurl) values (2,
2, 'http://www.ibm.com')
insert into links (linkid, userid, linkurl) values (3,
2, 'http://www.oracle.com')
go
insert into addressinfo (userid, addresstype, address)
values (1, 'hm', '1 Smith St, Toorak')
insert into addressinfo (userid, addresstype, address)
values (1, 'wk', '50 Swanston St, Melbourne')
insert into addressinfo (userid, addresstype, address)
values (2, 'hm', '20 Davis cr, Richmond')
insert into addressinfo (userid, addresstype, address)
values (2, 'wk', '265 Exhibition St, Melbourne')
go
create function user_links(@userid integer)
returns @user_links table (
userid int
, username varchar(200)
, linkid int
, linkurl varchar(7000)
)
as
begin
insert into @user_links (
userid
, username
, linkid
, linkurl
)
select u.userid
, u.username
, l.linkid
, l.linkurl
from users as u
inner join links as l on u.userid = l.userid
where u.userid = @userid

return

end
go
/* then, you could issue standard selects against the
function, eg:*/
select *
from user_links(2) as ul
go
/* even better - you can join other tables / views onto
the
function's resultset output, eg: */
select *
from user_links(2) as ul
join addressinfo as ai on ul.userid = ai.userid
go
drop function user_links
go
drop table addressinfo
go
drop table links
go
drop table users
go

HTH

Regards,
Greg Linwood
SQL Server MVP

[quoted text, click to view]

Arun[Symbiosis]
7/17/2003 8:56:14 PM

Views have definition which is the select statement.

The result returned by views are temporarily cached like
the resultset of any other select statement.

Every time you call the view execution plan is made and
resultset is created.

They are not stored in a virtual table.

Purpose of view is:

security

viewing of distributed data

really complex queries

[quoted text, click to view]

Ray at <%=sLocation%
7/17/2003 10:02:48 PM

Hi group,

Generally speaking, is there any sort of rule of thumb as to when it would
be better (faster, more efficient) to start using views instead of using
WHERE clauses all the time? For example:

Table: Users
UserID int, identity
'''other columns like username, password, etc.

Table: Links
LinkID int, identity
UserID FK
Link varchar(2083)

So, let's say that there are 50 users in this system, and each user has
about 350 links in the links table. Each time I'd want to retrieve a list
of links for a specific user, I'd do "select link from links where userid=1"
for example. Instead of that, I'm thinking of creating a view for each user
that contains only his links, and I could then just do "select link from
view1" for example.

I would be actually using other WHERE conditions in my selects though, like
"where categoryID=3 and dateadded>'2/1/2003', or other such things. So, it
almost seems to me that by using views, I'd essentially be doing two
queries - one that generates the view, and then one that I execute. But, it
only seems that way to me because I do not understand the mechanics behind
views, like if these are stored in memory or virtual tables, or if they are
"refreshed" each time they are called upon.

Thank you for any insight.

Ray at home

Ray at <%=sLocation%
7/17/2003 11:56:57 PM

Thank you both very much.

Ray at home

"Ray at <%=sLocation%>" <ray@ajf8jalskdfna.sefrhja7yasdf.com> wrote in
message news:ODmg9$MTDHA.2152@TK2MSFTNGP12.phx.gbl...
[quoted text, click to view]
<trimmed>

Steve Kass
7/18/2003 2:56:56 AM

Arun,

For the record, row-level security implemented through views
or table-valued functions is not always secure, although the
....where suser_sid = user_sid_column
is widely used.

Whether privileged information can be revealed depends
on quite a few things, but if no breach can be risked, something
should be added to force the view or function result set to be
materialized, such as

select top 2000000000 * from
....
order by <whatever is likely to be fastest>

Steve Kass
Drew University

[quoted text, click to view]

SriSamp
7/18/2003 9:01:24 AM

Rather than having views based on the number of users, a better solution
would be to encapsulate the retrieval logic inside a stored procedure and
just call the procedure with appropriate number of arguments.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp

"Ray at <%=sLocation%>" <ray@ajf8jalskdfna.sefrhja7yasdf.com> wrote in
message news:ODmg9$MTDHA.2152@TK2MSFTNGP12.phx.gbl...
[quoted text, click to view]

Reg Besseling
7/18/2003 2:38:15 PM

Ray,

IMHO

assume in your eg that you need info from multiple tables i would create 1
view that will hold all the result rows from the tables you want to join

e.g.
create view UserLinkInfo as
a.userid ,b.link, c.OtherInfoField
from users a inner join links b on
a.userid = b.userid
inner join OtherInfo c on
b.linkid = c.OtherInfoFK
where SomeCriteria = SomeOtherCriteria

The more joins and criteria the more complex it becomes (that is why we keep
it in a view) so when you want this info for a particular user you just
select * from UserLinkInfo where userid = 1

if categoryID=3 and dateadded>'2/1/2003' is going to be constant put it in
the view if not put it in the app

so the simple answer is if you have large amounts of common code keep that
in the view and filter with where clauses when you select from the view

Regards

Reg

"Ray at <%=sLocation%>" <ray@ajf8jalskdfna.sefrhja7yasdf.com> wrote in
message news:ODmg9$MTDHA.2152@TK2MSFTNGP12.phx.gbl...
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Views or WHERE