Press "Enter" to skip to content

Category: T-SQL

RegEx Performance in Azure SQL DB

Published 2025-03-20 by Kevin Feasel

Brent Ozar breaks the bad news:

Regular expressions are a way of doing complex string searches. They can be really useful, but they have a reputation: they’re hard to write, hard to read, and they’re even harder to troubleshoot. Once you master ’em, though, they come in handy for very specific situations.

This post isn’t about their complexity, though. This post is about Azure SQL DB & SQL Server 2025’s regex performance.

Brent’s testing hurts, because I want to use regular expressions, and based on what he’s seen so far, we’re probably still better off using CLR-based regex in SQLSharp.

Leave a Comment

Linting SQL with SQLFluff

Published 2025-03-19 by Kevin Feasel

Josephine Bush busts out a linter:

I thought I didn’t care about linting, and lately, I haven’t written a lot of SQL, but for the SQL I do write, I have SQLFluff to help me format it. A friend of mine is big into SQLFluff and finally talked me into installing and using it. For more information about SQLFluff itself, visit here.

Josephine shows off some of the configuration for PostgreSQL’s psql as well as SQL Server’s T-SQL.

Leave a Comment

SQL Server Views and Implicit Data Types

Published 2025-03-17 by Kevin Feasel

Kendra Little takes a peek at a view:

Views let you do dumb things by accident in SQL Server. Then they make you have to think way too hard to fix them.

Most of the time when people create views, they start by refining a SELECT query, then turn it into a view. People also often create multiple views that pull different slices of data and UNION the results together.

Combined, these two things easily lead to undeclared datatypes in views with problematic implicit conversions.

Read on for an example of this problem in action. Kendra’s example involved a view and a separate table, but you can also see this kind of thing pop up in a view that itself contains set operators like UNION.

Leave a Comment

SQL Server Performance Office Hours

Published 2025-03-17 by Kevin Feasel

Erik Darling answers a set of user questions:

You have said that table variables, CTEs, Change Tracking, and Azure Managed Instances all suck. Do you have a full list of “features” to avoid?

Click through for a video of Erik answering questions around deadlocks, terrible things, UTF-8, and more. And I like the nuance behind Erik’s answer of this particular question. It’s easy to say “this thing is awful” and be done with it, but often times, the answer is more of “In this particular circumstance, don’t use this thing because of reasons X, Y, and Z; instead, use this thing.” That’s a rather different answer.

Leave a Comment

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.

Leave a Comment

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?”

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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