Thats pretty much as i had thought,

are you able to post the rest of the code ?

Remember though , we are unpaid volunteers here and wont...

Something like this ?

SELECT LOC_NUM, sum(case DEPARTMENT WHEN 'CCD' THEN 1 else 0 end) AS CCD,

...

IMO , cursoring over 16 rows should really be causing you a lot of pain in itself.

-- EDIT Typo , "should" should be "shouldn't"

Still , is this what you...

IMO , you are asking the wrong question.

What are you doing with this data once it is in variables ?

If you tell us the whole problem then you may well...

If ORDER by is not specified , no order can be guaranteed.

I blogged about this a little while back

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/06/27/sql-101-without-order-by-order-is-not-guaranteed.aspx

This simplest methods of this are

1) Use "not exists"

Select Id from table1 where not exists(Select childid from table2 where Table2.ParentId = Table1.ID)

or

2) Left outer join

Select Table1.Id from Table1 left outer...

Try setting the ARITHABORT / ARITHIGNORE settings

http://msdn.microsoft.com/en-us/library/aa259212%28v=sql.80%29.aspx

Cool , glad you got there in the end.

For the icing on the cake, you should change the scalar udf into an inline one too.

Try including the delete_flag to both of the indexes.

Should resolve the nested loop lookup.

Google is your friend 🙂 , though it may not be...

Agreed, just commenting that the overall 'shape' is pretty much inline with what i was expecting.

Dont look to bad...

Can you post the "actual" plans though , you only posted the estimated

Please post both of the plans as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

What is the difference in time between

SELECT ca.adr_key,dbo.HaversineDistanceKm(@varLatitude,@varLongitude,ca.adr_latitude,ca.adr_longitude) latlonkm

FROM co_address ...

Throwing in the towel on this one...

http://www.sqlservercentral.com/Forums/Topic1048672-392-2.aspx#bm1049856

Am i not being clear in the issue ?.

--EDIT

Ok, he now gets it http://www.sqlservercentral.com/Forums/FindPost1049874.aspx