[dba-SQLServer] [AccessD] How important is specifying exact fields

Asgar,

The views I identified do in fact select specific fields from each table.  My question really has to 
do with the SQl Server optimizer stuff.

Let's assume I select 15 fields in a view.  I then use that view in a stored procedure where I 
narrow down the select statement to a set of 5 fields that I need for a specific combo.

Does SQL Server only actually pull the 5 queries into the view that it uses as the base for the 
selection in the SP?  Or does it pull all 15 fields and then pass all 15 to the SP to select which 
fields it wants to use?

IOW can I use a base view with 15 fields for 5 different SPs which need different subsets of fields.

John W. Colby
www.ColbyConsulting.com

On 6/19/2011 6:23 PM, Asger Blond wrote:
> First, just a warning against the use of SELECT * in SQL Server views and table valued functions.
> In Access the fields for a SELECT * is resolved at run time for a stored query, which means that the query will always return the fields existing in the table at the moment the query is executed.
> In SQL Server things are different: A SELECT * is resolved at run time only for stored procedures, for views and table valued functions the fields are resolved at design time. This means that a view or a function might give unexpected results or fail if you have dropped or added fields in the tables referenced in the view or table valued function.
> To see the different behaviours try out this in SQL Server:
> ---
> CREATE TABLE T(C1 int)
> INSERT INTO T VALUES(1)
> GO
>
> CREATE VIEW vwT AS
> SELECT * FROM T
> GO
>
> SELECT * FROM vwT
> GO
>
> CREATE PROCEDURE uspT AS
> SELECT * FROM T
> GO
>
> EXEC uspT
> GO
>
> CREATE FUNCTION udfT1() RETURNS table AS
> RETURN (SELECT * FROM T)
> GO
> SELECT * FROM udfT1()
> GO
>
> CREATE FUNCTION udfT2() RETURNS @t table (C1 int) AS
> BEGIN
> 	INSERT INTO @t SELECT * FROM T
> 	RETURN
> END
> GO
>
> SELECT * FROM udfT2()
> GO
>
> ALTER TABLE T ADD C2 varchar(11)
> GO
>
> UPDATE T SET C2='x'
> GO
>
> SELECT * FROM T
> GO
>
> SELECT * FROM vwT-->  only the first field returned
> GO
>
> EXEC uspT-->  both fields returned
> GO
>
> SELECT * FROM udfT1()-->  only the first field returned
> GO
>
> SELECT * FROM udfT2()-->  fails
> GO
>
> ---
>
> So comparing Access and SQL Server: a stored query in Access should be equated to a stored procedure in SQL Server, not to a view or a table valued function.
>
> Second, and for the main part of your question, it certainly will reduce overhead and bandwidth if the sp only returns the wanted fields. You can narrow down the wanted fields in the sp by using a parameter in the sp and then branch the select statement according to the supplied parameter. I.e. (air code):
>
> CREATE PROCEDURE sp @place int AS
> IF @place = 1
> SELECT ...,...,... FROM ...
> IF @place = 2
> SELECT ..., ... FROM ...
> GO
>
> Asger
>
>
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby
> Sendt: 19. juni 2011 03:18
> Til: Sqlserver-Dba; Access Developers discussion and problem solving
> Emne: [AccessD] How important is specifying exact fields
>
> I am developing a strategy for selecting locations and AA meetings around a prison camp.  My
> strategy is to have a table of cities "associated" with a camp through a m-m table.  I also have
> camps "associated" with a volunteer through a m-m table.
>
> Once I do this I want to select meetings / locations specific to the logged in volunteer.  This
> involves a rather long set of joined tables as you can imagine.  Volunteer to VolunteerCamp to Camp
> to CampCity to City to Location for the first view, and from there to LocationMeeting.
>
> I basically created the view, then I created a SP with a SELECT * FROM MongoView WHERE
> MongoView.IDVol = @VolID.  @VolID is passed in to the SP and is used to filter and return only the
> Locations (for the first SP) or the meetings (for the second SP)  The results of the SP is then used
> in combos to select locations and meetings.
>
> My question then is, how important is it to minimize the fields pulled along the way.  I really have
> to have the VolID from the first (leftmost) table and many of the fields from the last (right most
> table).
>
> The point of this whole thing is to narrow down from hundreds or thousands of locations and meetings
> only those relevant to the specific volunteer.
>
> It just occurred to me I could have done this differently and "associat" cities to each volunteer
> rather than to the camp.  This would allow each volunteer to decide what cities (s)he cares about
> locations / meetings in.
>
> In any event, the view / SP pulls a very small hand full of locations so do I really care if it
> pulls a few fields I don't need in the end?  It seems like I might be using the same query for
> several different SPs which require different sets of fields.
>
> IOW should I custom build a view and SP for for each place I need it, such as these combos?  Or
> build a hand full of more general views used in more places.
>
> Also, if I do add more than the necessary fields in the View, can I narrow it down in the SP?  IOW
> select the specific fields in the SP instead of Select * where every field is necessary.
>
> This is the first time I have started using views / SPs in SQL Server to drive Access and so I don't
> really trust my instincts.
>