[AccessD] How important is specifying exact fields

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