return first result | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

return first result

I have a data table of projects and if they are a child of a project I store the parents project id in the child project. Right now I run a subquery call isparent that checks for a match. I use SELECT TOP 1 ID. Does sql have some kind of FIRST keyword which if it finds a result then it stops looking for more? I assume for the TOP command sql builds the result set and then returns the top ones, but I don’t want it to build the result set since I don’t care about specific information, just that there is information. Any ideas? <br /><br />SELECT TOP 100 PERCENT dbo.tblProjectData.ProjectID,dbo.tblProjectData.projPMOID, dbo.tblProjectData.ProjNameShort, dbo.tblProjectData.NewCostCtr, dbo.tblPeopleLinkType.typename, <br />dbo.tblProjectData.WebsiteAvailable, dbo.qryDocumentCount.DocCount, dbo.tblProjectData.LocatorShow, dbo.tblProjectData.ProjName, <br />dbo.tblProjectData.StrategicProcess, dbo.tblProjectData.Portfolio, dbo.tblProjectData.TechLayer1, dbo.tblProjectData.TechLayer2, <br />dbo.tblprojectpeoplelink.personid, dbo.tblPeopleLinkType.typeid, dbo.tblProjectData.Status, dbo.tblProjectData.TempStatus,<br />dbo.tblprojectpeoplelink.linktypeid,<br /><br />(SELECT TOP 1 pd1.ProjectID FROM dbo.tblProjectData pd1 WHERE pd1.ProjNoShow=0 AND pd1.StateID IN (1,2,3,4,5,7,13,14,15,16,17,1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> AND (pd1.Program=dbo.tblProjectData.ProjectID OR pd1.ProjInitiative=dbo.tblProjectData.ProjectID OR pd1.SubProject=dbo.tblProjectData.ProjectID)) as isparent<br /><br />FROM dbo.tblProjectData INNER JOIN<br />dbo.tblprojectpeoplelink ON dbo.tblProjectData.ProjectID = dbo.tblprojectpeoplelink.projectid INNER JOIN<br />dbo.tblPeopleLinkType ON dbo.tblprojectpeoplelink.linktypeid = dbo.tblPeopleLinkType.typeid LEFT OUTER JOIN<br />dbo.qryDocumentCount ON dbo.tblProjectData.ProjectID = dbo.qryDocumentCount.DocProjID<br />WHERE<br /> dbo.tblprojectpeoplelink.projectid IN (*List of projects*) AND<br /> dbo.tblprojectpeoplelink.personid=*myID*<br />ORDER BY dbo.tblprojectpeoplelink.Prioritize<br /><br /><br />
sql tries the most efficient op possible
if you just say top 10, it stops looking after it finds 10,
unless you do an order by, where it finds all, then sorts to return the top 10 look into the use of EXISTS keyword
]]>