FIX: Select from Nested Views May Not Return Result After SQL Server Updates Statistics (308757)

SYMPTOMS

A SELECT from some nested views may not return results after SQL Server updates statistical information.

For example:

 SELECT * FROM NestedView

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

To work around this problem you can either:

Remove the GROUP BY clause from the view if you use a GROUP BY. For example, remove the GROUP BY clause in the CommonView.

-or-
Create multiple copies of the same view. Each outer view uses a different inner view. For example, ViewA uses CommonView, ViewB uses CommonView1 and so forth.

NOTE: CommonView and CommondView1 have the same view definitions. Refer to the "More Information" section of this article for view definitions.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

MORE INFORMATION

Definition of the Views

Here is a code sample that defines the views:

CREATE VIEW NestedView
AS
SELECT 
    ViewA.ColA, ViewB.ColB,
FROM Table1 LEFT OUTER JOIN 
    ViewA ON 
    Table1.Col1 = ViewA.Col1 AND 
    Table1.Col2 = ViewA.Col1 LEFT OUTER JOIN
    ViewB ON 
    Table1.Col1 = ViewB.Col1 AND 
    Table1.Col2 = ViewB.Col2

CREATE VIEW ViewA
AS
SELECT <ColLists>
FROM CommonView
WHERE <ConditionsA>
GROUP BY <ColLists>

CREATE VIEW ViewB
AS
SELECT <ColLists>
FROM CommonView       
WHERE <ConditionsB>
GROUP BY <ColList>

CREATE VIEW CommonView AS
SELECT <ViewsList>
FROM < INNER_JOINs >
GROUP BY <ViewsList>    
HAVING <Conditions>
<BR/>