I'm trying to understand how JOINS are handled, and was wondering about the following:
Imagine a big query concerning a lot of tables, I'm really looking for small optimizations as it's a pretty important query. What would be faster in the long run. The 'normal':
SELECT TOP x A.a , A.xxx , B.a , B.xxx FROM A LEFT JOIN B ON (A.xxx = B.xxx) ORDER BY A.a
Or specifying the columns of the joined table beforehand?
SELECT TOP x A.a , A.xxx , B.a , B.xxx FROM A LEFT JOIN ( SELECT B.A AS A , B.xxx AS xxx FROM B ) AS B ON (A.xxx = B.xxx) ORDER BY A.a
I guess this boils down to having a good understanding of how SQL Server handles optimization. In my preliminary tests I haven't found any difference, but these tests aren't quite as complicated as 'The Real Thing'. Will the latter just be optimized to the former by the interpreter?
Since I have never seen the latter side being used (but admittedly, I'm really new to this), my gut feeling tells me the first one will be preferred, but a gut feeling won't (or shouldn't) hold up as real evidence.
Bare in mind the real query really has alot of tables involved with probably only 1 or 2 relevant columns per table, so I was thnking perhaps I can doe some sort of preselection to avid including the whole table in the query.
So, any ideas?