Press "Enter" to skip to content

Category: T-SQL

RIGHT OUTER JOIN and MERGE

Published 2025-03-14 by Kevin Feasel

Lukas Eder merges lanes:

It’s not unreasonable to expect these two statements to produce the same execution plan on most RDBMS, given that they’re logically equivalent. Since we’ve grown used to reading things from left to right and top to bottom, I don’t think RIGHT JOIN will become more popular any time soon.

There is, however, one place in the SQL language where RIGHT JOIN is surprisingly ubiquitous!

Read on to see how the MERGE operator includes as part of its operations the equivalent of a RIGHT OUTER JOIN. MERGE in SQL Server had justifiably built a bad reputation in its early years, but it’s surprisingly okay nowadays, with most of the bugs being fixed over time.

Comments closed

Alternative Means to Build a Tally Table

Published 2025-03-13 by Kevin Feasel

Steve Jones tries different methods:

We published an article recently at SQL Server Central on Tally Tables in Fabric from John Miner. In it he showed how this can be efficient. A day after he published it, he sent me an addendum to note that GENERATE_SERIES was available in Fabric and that could be used.

I ran a few tests last week, but as I read the comments on John’s article, I realized that there were 3 ways of setting up these tally tables that I’ve used and thought I’d summarize them a bit in this post. There’s a fourth way, but I haven’t used it.

My take on this is, it doesn’t really matter which technique you choose, especially if you’re using it to build a permanent tally table that you can later query from any app. In that case, it’s a one-time cost. If you’re building these on the fly often enough that the performance matters, then my first question, instead of “Which of these is fastest?” is, “Why can’t I just have a permanent tally table?”

Comments closed

The Order of Data Conversion and Aggregation Functions

Published 2025-03-12 by Kevin Feasel

I have a new video:

In this video, I demonstrate how order of operations matters when it comes to casting or converting a data type and performing an aggregation on that result. I’ll use the specific example of converting binary data to a number and show where the fast version might lead you astray.

This is something pretty easy to miss, especially when the code returns fast enough. But over a large enough number of calls, these sorts of things add up, as I note in the video.

Comments closed

Solving Linear Equations in SQL Server

Published 2025-03-11 by Kevin Feasel

Sebastiao Pereira implements a function:

Solving linear equations is essential for solving real-world problems in Science, Engineering, Data Analysis, Machine Learning, Economics, Finance, and other areas. Is it possible to have a tool to solve linear equations directly in SQL Server? We will look at how to create a Gauss-Seidel method function for SQL Server.

This is one way to solve a series of linear equations, and it’s a pretty neat implementation.

Comments closed

Handling SQL Agent Dates and Durations

Published 2025-03-10 by Kevin Feasel

Andy Mallon disparages some Microsoft intern’s summer of 1996 project:

SQL Agent’s schema is older than me. It handles dates, times, and durations like it’s 1980 by using integers instead of date/time data types. My buddy Aaron Bertrand talks more about Dating Responsibly so that you can have a good datetime with your own database.

I was writing a query to pull recent job failures from SQL Agent’s msdb job history, and knew that I didn’t want to deal with the wonky date/time formats. Specifically, I was querying msdb.dbo.sysjobhistory to find the Start Time, End Time, and Duration of job runs that failed. If you aren’t familiar with that table, you can look at it over in the docs.

Andy does point out the built-in function but then explains why a separate function is superior. Andy also happens to furnish that function, so check it out.

Comments closed

Performance Comparison: Tally Table vs GENERATE_SERIES()

Published 2025-03-06 by Kevin Feasel

Steve Jones performs a pair of tests:

I had someone reach out about generate_series() recently, saying they hadn’t realized this was a new feature in SQL Server 2022. They were wondering if it was better than using a tally table.

I didn’t want to do an exhaustive test, but I thought I’d take a minute and try a couple simple things just to see.

Steve used the CTE-based tally table builder, building based on cross joining spt_values. This is one of the classic approaches. The performance differences aren’t enough on their own to justify large-scale changes if you’re using a classical tally table, though it is good to see that GENERATE_SERIES() does perform well. And if you’re not familiar with the power of a tally table, here is one great explanation of the concept.

Comments closed

Avidity KPIs in T-SQL

Published 2025-03-05 by Kevin Feasel

I have a new video:

In this video, we will take a look at two KPIs for measuring avidity. We will also show off how to use ranking window functions to order groups of customers.

Click through for the video. There are far too many measures of avidity for me to do a good job explaining them all, and so many of them are closely tied to the specific nature of the business, but hopefully this at least gives you ideas of how the business side may look at user avidity or stickiness.

Comments closed

Making a Query SARGable

Published 2025-03-05 by Kevin Feasel

Haripriya Naidu explains SARGability:

Having the right index is helpful, but are you using the predicate (WHERE clause) correctly to make efficient use of that index?

This is where the term SARGable comes into play. SARGable stands for Search ARGumentable. If SQL Server is able to limit the search space while evaluating the predicates and can seek right at the page(s) to get the values, then it is SARGable.

Read on for an explanation of why this is important, as well as several examples of what is SARGable versus what isn’t. The most important thing about SARGability is that you pronounce it like “Sarge” and not “sarg.”

Comments closed