[dba-SQLServer] Views

Arthur,

I'm not following you.  I create a view, then use it in a stored
procedure:

	CREATE PROCEDURE [dbo].[procMyStoredProcedure] 
	@MyVariable int
 	as
 
 	Select Field1, Field2, Field3 from uvwMyView where  Field5  =
@MyVariable

What am I losing?  I'm still returning only the records where my
condition is met.


Thanks,   


Liz   

 
 



-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Tuesday, October 07, 2008 3:00 PM
To: Discussion concerning MS SQL Server
Subject: Re: [dba-SQLServer] Views

Didn't I already answer that question, Susan? Views are a wonderful
convenience if you have a smallish number of rows, but the overhead
seriously adds up when you pass, say, 50K rows in a table -- let alone
50M rows.

SELECT * FROM MyView WHERE PK = 124

results in the case of a view that all rows in the view are sent to the
client and then the client filters the result set. By contrast, a sproc
that accepts a @PK parameter will execute on the server and transmit
only the result set over the pipe, This could be a difference of
hundreds of thousands or even millions of rows. Multiply that by the
number of users.

Views are cute and fun, but essentially stupid. That said, I build them
all the time, because I get to use the designer to build them, but then
I save them and grab the SQL generated by the designer and turn it into
a parameterized sproc or table udf.

A.

On Tue, Oct 7, 2008 at 3:23 PM, Susan Harkins <ssharkins at gmail.com>
wrote:

> Why?
>
> Susan H.
>
>
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

This message may contain confidential and/or privileged information. If
you are not the addressee or authorized to receive this for the
addressee, you must not use, copy, disclose, or take any action based on
this message or any information herein. If you have received this
message in error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.