Incorrect Results with t-sql Query in SQL Server 2005
I'm seeing some change in behavior for a query in SQL Server 2005 (compared to behavior in SQL Server 2000). The query is as follows:
create table #projects (projectid int) insert into #projects select projectid from tblprojects where istemplate = 0 and projecttemplateid = 365
Select distinct tblProjects.ProjectID
from tblProjects WITH (NOLOCK)
inner join #projects on #projects.projectid = tblprojects.projectid
Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID
and tblProjects.projectID in (
where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
and enabled = 1 )
This is dynamic SQL generated by the application when a user requests a report with variable parameters. It works fine in SQL Server 2000. It outputs 47 records which is correct.
In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record. (Results of Showplan Text attached).