[AccessD] How important is specifying exact fields

Hello Asger,

First of all, thank you for your fascinating email.  I never noticed that
aspect of Views.

On first reading it, I thought "it cannot be right".

However, of course, it is right.

I see now that when I type into SSMS

CREATE VIEW vw_test AS
SELECT * FROM mytable

it actually explicitly pulls the columns from the table and politely creates
the table.  However, as you are pointing out, it does not really create the
view with the SQL that I typed i.e., Select *, it resolves it to the columns
that currently exist.

While I have used that feature many times in the past to generate a list of
columns without having to explicitly type them, I never noticed that it is
not actually possible to Select * in a view.

thanks for teaching me something new today,

Mark


On 19 June 2011 23:23, Asger Blond <ab-mi at post3.tele.dk> 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.
>
> --
> John W. Colby
> www.ColbyConsulting.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>