SQL Server Query

Suppose we have a table Category and another table Item.
In Item table we have CategoryId column which is a foreign key derived from Category table.

The following is the query:
SELECT * FROM Item A
JOIN Category B ON A.CategoryId = B.CategoryId
WHERE ItemName LIKE '%Tea%'

The above query is executed in the following sequence:
1. A dataset is created from Item Table and incase there are Columns in where clause, these are also resolved while creating the dataset. Hence,
A dataset will be created from Item table in which all the items contain word "Tea".
2. The same is applied for the table Category.
3. Then both the datasets are joined to form single dataset consisting of data and at this point the joining condition is resolved.
4. Finally the dataset is returned.

The above sequence can be verified for a query by viewing its execution plan. I used SQL 2008 for this.

Hope this answer your question.

It depends - internally SQL server will rewrite your query to optimize performance. It looks at meta information and collected statisticts - and then creates execution paths based on the available information.

Take a look at:
http://www.sql-server-performance.com/tips/hints_general_p1.aspx[^]

Regards
Espen Harlinn

Have a look and read here: SELECT (Transact-SQL)[^]

All your questions about the order should be answered via it!
P.S.: There is a link[^] for examples too, at the bottom of article.