What is difference in performance of following 2 queries

What is the difference in performance of following 2 queries in SQL Server 2008

Query 1:

SELECT A.Id,A.Name,B.Class,B.Std,C.Result,D.Grade 
FROM Student A
INNER JOIN Classes B ON B.ID = A.ID
INNER JOIN Results C ON C.ID = A.ID
INNER JOIN Grades D ON D.Name = A.Name
WHERE A.Name='Test' AND A.ID=3

Qu开发者_如何学JAVAery 2:

SELECT A.Id,A.Name,B.Class,B.Std,C.Result,D.Grade 
FROM Student A 
INNER JOIN Classes B ON B.ID = A.ID AND A.Name='Test' AND A.ID=3
INNER JOIN Results C ON C.ID = A.ID
INNER JOIN Grades D ON D.Name = A.Name

Is there any best way to achieve the best perofermance in the above 2 queries

You can have a 100% guarantee that they execute the same, with the same plan.

The only time it does matter when splitting AND/WHERE clauses in an INNER JOIN is when options like FORCE ORDER is used.

A (id, name)
B (id) includes (class, std)
C (id) includes (result)
D (name) includes (grade)

However, it still depends on your distribution of data and selectivity of indexes as to whether they will actually be used. e.g. if your Grade table contains only 5 entries A,B,C,D,E, then no index will be used, it will simply scan and buffer the table in memory.

If you look at the execution plans of each, you'll find they're in all likelihood going to have identical execution plans.

The way to best improve performance for these kinds of queries is to make sure that you've got the proper indexes set up. All of your ID columns should have indexes on them, and if Grades is a largeish table, consider putting an index on Name for both it and Student