TOP used before the predicate – feels like a Sql bug but certainly i am missing something
I checked BOL and it doesn't mention the processing order but everything i knew about logical processing says the predicates/joins are first then the projection and finally the TOP and order bys. Based on the link below and an image by Jet Wang in the thread. This seems to back up my thoughts. But certainly i am missing something. This seems too rudimentary for it to be a sql bug. thoughts?
SELECT TOP (1) 'g' objecttype, v.vehicleid, v.runid, v.rundate, v.id, startdatetime, 1--matches the CROSS JOIN for Pickup FROM dbo.tblRunactual v WHERE v.verified = 1 ORDER BY v.StartDateTime
…is its own scope. The optimizer can move things around within a query (even across scopes) but it must retain the original semantics.
The semantics of your query is that the scope above is logically evaluated before the outer filter is applied. It may not be what you intended, but nevertheless it is correct in SQL.