Should I use the old Microsoft JOIN syntax, or the ANSI JOIN syntax for the best JOIN performance?


Which of the following joins will produce better performance?


SELECT fname, lname, department
FROM names INNER JOIN departments ON names.employeeid = departments.employeeid

Former Microsoft JOIN Syntax

SELECT fname, lname, department
FROM names, departments
WHERE names.employeeid = departments.employeeid


SQL Server supports two variations of performing JOINs: the ANSI JOIN syntax and the former Microsoft JOIN syntax. Both produce identical results and identical performance. There is no performance reasons to use one form of the JOIN over the other.

On the other hand, there are two good reasons why you should use the ANSI JOIN syntax over the former Microsoft JOIN syntax. First, it is more portable because it is the ANSI standard, and second, because eventually Microsoft may eliminate support of the former JOIN syntax.


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |