Microsoft KB Archive/250354
Article ID: 250354
Article Last Modified on 10/16/2002
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q250354
BUG #: 57305(SQLBUG_70)
In certain circumstances, the SQL Server optimizer may generate a handled Access Violation (AV) when attempting to create a Hash Join Plan. A sample of the table definition and the query that generates the AV follows:
Use Pubs Go CREATE TABLE dbo.Tbl ( Col1 varchar(35) NOT NULL , Col2 money NOT NULL ) Go
SELECT T1.Col1 FROM ( SELECT T1.Col1, SUM(T1.Col2) AS Col2 FROM Tbl T1 GROUP BY T1.Col1 ) T1 FULL OUTER JOIN ( SELECT T2.Col1 , SUM(T2.Col2) As Col2 FROM Tbl T2 GROUP BY T2.Col1 ) T2 ON T1.Col1 = T2.Col1 WHERE (T2.Col2 - T1.Col2) < 0 Go
Here are a few ways you can work around this problem:
- Create a Clustered Index on Col1.
- Issue a SET FORCEPLAN ON statement and then execute the SELECT.
- Replace the FULL OUTER JOIN with a FULL OUTER MERGE JOIN or a FULL OUTER LOOP JOIN.
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For information about how to download and install the latest SQL Server Service Pack, see the following Microsoft Web site:
For more information, contact your primary support provider.
The following behavior was also observed:
- Changing the datatype of Col2 to INT or NUMERIC does not change the behavior.
- Using just the SHOWPLAN_ALL ON option or the STATISTICS PROFILE ON option also causes the AV. This is because the AV occurs when the optimizer tries to create an execution plan.
- Changing the operator in the WHERE clause does not change the behavior.
- Removing the WHERE clause ( WHERE (T2.Col2 - T1.Col2) < 0 ) does not cause the AV.
- The number of rows present in the table also influences the plan the optimizer chooses. In this case, a larger number of rows causes the optimizer to consider a Hash join resulting in an AV.
Keywords: kbbug kbfix KB250354