[AccessD] How important is specifying exact fields

John,

Take a look at derived tables.  You can probably do this using that. 
The  optimizer likes it quite well.

Here is a sample on one that I have put together:

There is a derived table from a UNION query used by a derived table 
summing the results from the union then it is used by the main view:

SELECT     dbo.tbl_Zip_Code.ZipCode_Key, dbo.tbl_Zip_Code.ZipCode, 
CASE WHEN dtUsageCount.UsageCount IS NULL
                       THEN 0 ELSE dtUsageCount.UsageCount END AS 
LookupUsageCount
FROM         dbo.tbl_Zip_Code LEFT OUTER JOIN
                           (SELECT     ZIP_Code_Key, SUM(Expr1) AS UsageCount
                             FROM          (SELECT     ZIP_Code_Key, 
COUNT(NameAddressID) AS Expr1
                                                     FROM 
dbo.tbl_NameAddress
                                                     GROUP BY ZIP_Code_Key
                                                     UNION ALL
                                                     SELECT 
ZIP_Code_Key, COUNT(AddressHistID) AS Expr1
                                                     FROM 
dbo.tbl_ClientAddressHistory
                                                     GROUP BY 
ZIP_Code_Key) AS dtUsageCount_1
                             GROUP BY ZIP_Code_Key) AS dtUsageCount 
ON dbo.tbl_Zip_Code.ZipCode_Key = dtUsageCount.ZIP_Code_Key

By the way, this will also work with MS Access as well as SQL Server. 
But the code above is for SQL Server.


At 07:59 AM 6/20/2011, you wrote:
>Date: Mon, 20 Jun 2011 08:59:46 -0400
>From: jwcolby <jwcolby at colbyconsulting.com>
>To: Access Developers discussion and problem solving
>         <accessd at databaseadvisors.com>,         Sqlserver-Dba
>         <dba-sqlserver at databaseadvisors.com>
>Subject: Re: [AccessD] How important is specifying exact fields
>Message-ID: <4DFF4442.8040109 at colbyconsulting.com>
>Content-Type: text/plain; charset=UTF-8; format=flowed
>
>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

Robert L. Stewart
www.WeBeDb.com
www.DBGUIDesign.com
www.RLStewartPhotography.com