Press "Enter" to skip to content

Category: T-SQL

Building a RegEx Emulator in SQL Server

Published 2025-02-26 by Kevin Feasel

Sebastiao Pereira offers a fourth-best solution:

Regular expressions (REGEX) let you adaptively investigate, employ, and authenticate text data. This makes it easy to search for simple or complex string patters. There is no direct way to do this in SQL Server, but in this article we look at some SQL functions you can create to emulate regex like functionality.

Regular expressions are coming to SQL Server 2025 and are in Azure SQL Database, so that’s the best option when it becomes available. The second-best option is to use CLR and offload your regular expressions work to .NET, especially if you’re using a library like SQLSharp to do so. The third-best option would be to do this in Python or R with ML Services, though that’s going to be a bit of setup effort and will probably be somewhat limiting. And if all else fails, this is an admirable fallback.

Comments closed

Maxing Out on Stored Procedure Parameters

Published 2025-02-26 by Kevin Feasel

Louis Davidson tries it out:

So I replied: “Challenge Accepted”, well, actually I replied with a gif of Neo saying it, but the effect was the same. So, I decided to just see, what would that look like. Coincidentally I am testing the new template for the Simple Talk site, and a function with 2100 parameters seemed like some code that screams out: “BIG!” Testing is should always be about pressing the limits of your code, so why not.

It turns out that you cannot have 2101 parameters in a single stored procedure definition. But this is definitely an example of Swart’s 10% Rule. It also nets Louis my most coveted category: Wacky Ideas.

1 Comment

Working with JSON_OBJECTAGG() and JSON_ARRAYAGG() in Azure SQL

Published 2025-02-25 by Kevin Feasel

Koen Verbeek tries out a couple of fairly new functions:

I need to construct JSON from data in our database, but I find the existing FOR JSON PATH limited when the data is not located in one single row but rather scattered over multiple rows. Is there another method on how to handle JSON data in SQL Server? Learn how to use the new SQL Server JSON Functions JSON_OBJECTAGG and JSON_ARRAYAGG in this article.

These aren’t available on-premises yet, though given that there’s a new version of SQL Server coming out in 2025, there’s a good chance we’ll have it by then.

Comments closed

Customer KPIs in T-SQL

Published 2025-02-19 by Kevin Feasel

I have a new video:

In this video, we will take a look at several useful KPIs for measuring customer growth. We will also demonstrate the utility of DATETRUNC(), calendar tables, and the LAG() and LEAD() window functions.

The calendar table (or date dimension) interlude may seem a little bit weird at first, but I firmly believe that calendar tables absolutely belong on basically every SQL Server instance, even if only in a utility database.

Comments closed

Financial KPIs in T-SQL

Published 2025-02-17 by Kevin Feasel

I continue a series:

In this video, we will dive into three of the most common financial key performance indicators: revenue, cost, and profit. We’ll also take a look at several T-SQL techniques, including aggregation, window functions, and common table expressions.

Click through for the video. This is the first video in the series in which I really dig into specific KPIs and SQL techniques.

Comments closed

Experimenting with BIT_COUNT

Published 2025-02-10 by Kevin Feasel

Louis Davidson has an idea:

I was editing an article the other day that uses the BIT_COUNT function that was added to SQL Server 2022. The solution presented is excellent, and I will try to come back and link to it here when I remember. (It will be linked the other way.

Reading that did two things for me. First it cave me an idea of how the BIT_COUNT function might be actually be used in a useful way. The solution that was presented would only work in SQL Server 2022 (It will work in earlier compatibility levels, based on the tests I have done.)

Read on for what Louis tried out.

Comments closed

COALESCE() in T-SQL

Published 2025-02-06 by Kevin Feasel

Rajendra Gupta has a backup plan in case of NULL:

NULL is a special marker that indicates a missing or undefined value in a column. It is different from zero or an empty string. Handling NULL values is essential for accurate data analysis, data integrity, and error avoidance. This tip explores how to handle NULL values in SQL Server using the COALESCE() function using various queries and reviewing the results.

Click through for a primer on the COALESCE() function, a few use cases for COALESCE(), and how it differs from ISNULL().

Comments closed

The Logic behind RIGHT OUTER JOIN

Published 2025-02-05 by Kevin Feasel

Constantine Kokkinos provides an explanation:

I was talking to a friend of mine and they are learning some SQL and they said something that I have seen come up multiple times in learning SQL.

They said “Yeah, I need to study the join types more. They make sense to me but I want to be able to not reference my notes” and also “I don’t really get the point of a right join if your can do the same thing with a left join by just switching the table name.”

These are great points, and common questions that occur when first learning SQL.

I won’t steal CK’s thunder (too much) about how we express joins in set theory, though I think when he mentions “OUTER” as a type of join, perhaps that’s supposed to be FULL OUTER JOIN?

Regardless, my take: there is a good reason to use INNER JOIN. There is a good reason to use LEFT OUTER JOIN. There is a good reason to use CROSS JOIN. There is a good reason to use FULL OUTER JOIN. The frequency in which you should use each is in descending order, meaning that there are relatively few circumstances in which you should use a FULL OUTER JOIN, but they do exist.

There are no good circumstances for a RIGHT OUTER JOIN. The concept logically exists, but has no practical value to us.

Comments closed