[dba-SQLServer] How important is specifying exact fields

jwcolby jwcolby at colbyconsulting.com
Sat Jun 18 20:18:26 CDT 2011


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


More information about the dba-SQLServer mailing list