A Tale Of Two Queries
I teased this blog post a little while back at SQLBits, but I’m finally getting a chance to finish this blog post. No stale, scheduled content here – it’s always hot off the presses.
Anyway, at Bits I did a few sessions, one of which was on AG and FCI troubleshooting (I’m also doing one tomorrow night at Boston SQL). As I was putting the talk together, one of the things I came up with was around endpoints for AGs. How an endpoint is fully configured is not exposed in SSMS, but you can query SQL Server’s dynamic management views (DMVs) which is easy enough if you know what you are looking for.
I did some of the initial documentatio for Microsoft a few years ago for distributed availability groups. Since its publication, Microsoft has opened it up and anyone can suggest edits/comments via GitHub. So far so good – all of this plays into the bigger story.
Flash back to last summer. I saw this on Twitter from Glenn Berry which spawned into a whole thread (and to see the whole thread, click on the date)
OMG, why does Microsoft still use ANSI-89 join syntax in new code samples? #sqlserver https://t.co/mqEdiUmtnv
— Glenn Berry (@GlennAlanBerry) July 12, 2018
When I wrote that documentation (and it was before 2018, if you care about such things), I used … gasp … a WHERE clause, not JOIN ON syntax. I’ve always written queries like that going back to the 90s. The gist of that thread was that some preferred the “newer” syntax like INNER JOIN, and subsequently, had the query changed to show that. You get the same results, but whatever. I didn’t take it personally and the thread was good natured.
Before you all start screaming at me they are right (no, they were not … more on that in a minute): yes, I’m old. Discrete math was a required course for me for my Computer Science degree. It wound up being fate that things like sets, joins, etc., became the foundation for my career. I’m not ignorant. These days I’m much more of an infrastructure guy, but I do write infrastructure-related queries (hence the AG stuff).
Pedro Lopes who is now on the SQL Server dev team wrote the blog post “T-SQL Misconceptions – JOIN ON vs. WHERE” which does a good job of explaining the “it depends” around the newer style of joins. Why link this article? Microsoft deprecated the OUTER JOIN operators in SQL Server 2008. Two other sources of information on this are here and here (the first is Ward Pond’s old technet blog, and sadly will probably go away soon). If you’re keeping score at home, WHERE clauses are not deprecated except if you’re using *= and =*). The changes people made were wholly unnecessary and as the author, the newer stuff is harder to decipher than what I originally did. They were putting their own biases onto things.
As an experiment, I wrote the endpoint DMV query in two variants: my preferred way (WHERE clause) and with the JOIN ON syntax. First up is the query execution plan for the WHERE clause. Click to make it bigger.
Figure 1. Query using a WHERE clause
Next up is the INNER JOIN
Figure 2. Query using a INNER JOIN clause
Spoiler alert: they have the same execution plan. There is no difference between
Figure 3. The actual WHERE clause
and
Figure 4. The actual INNER JOIN clause
From a results standpoint, you get the same output. In other words, zero difference all around.
Preferences are just that. Preferences. Don’t foist them on others. Deal?