FIX: GROUP BY ALL on a View Shows Columns Not in View (183726)

SYMPTOMS

Transact-SQL provides the ALL keyword in the GROUP BY clause. ALL is meaningful only when the SELECT statement in which it is used also includes a WHERE clause.

If you use ALL, the query results include all groups produced by the GROUP BY clause, even if some of the groups do not have any rows that meet the search conditions. Without ALL, a SELECT statement that includes GROUP BY does not show groups for which no rows qualify.

When using a GROUP BY ALL clause against a view, all of the columns that exist in the underlying table are inadvertently included in the results, even though they are not used in the view.

WORKAROUND

To work around this problem, do one of the following:

Obtain the hotfix mentioned in the STATUS section of this article.

-or-
Create a table that does not contain any data, and use that table in one side of the UNION.

-or-

Use statements as in the following example:

CREATE VIEW vYes
      AS
      SELECT  fldname
      FROM     tblYesNo
      WHERE  fldname='YES'
      UNION
      SELECT  fldname
      FROM     tblYesNo
      WHERE  fldname='YES'

NOTE: Using workarounds other than applying the hotfix results in performance degradation.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.0.

Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a