Category: Syntax

Erik Darling defends the honor of the MERGE statement:

In this video, I delve into the often maligned `MERGE` statement in SQL Server, defending it against its critics and highlighting its unique benefits. You might be surprised to learn that `MERGE` can actually help catch non-deterministic updates—something a standard `UPDATE` query won’t do. By demonstrating how `MERGE` issues a warning when you attempt a non-deterministic update, I aim to show why this statement deserves more respect in your database toolkit. The video also covers practical solutions for making these updates deterministic using window functions like `ROW_NUMBER()`, ensuring that the data integrity and consistency you expect from SQL Server are maintained. Whether you’re looking to improve query reliability or just want to understand a lesser-used feature better, there’s something here for everyone who deals with complex data operations in SQL Server.

Alongside this, I recommend a post from Hugo Kornelis from a few years ago, looking into pretty much every problem people reported with MERGE and checking to see if it was still and issue and, if so, under what circumstances.

Leave a Comment

Louis Davidson breaks out the stopwatch:

I have read so much lately about how bad it is to use a recursive CTE to do… well pretty much anything. It came up in a discussion about creating sequential number, and not in a positive sort of way.

I wrote about recursive CTEs when they were first added to SQL Server, and have been a fan for doing breadth-first searching of a hierarchy/graph, but never even thought to use one to generate a set of numbers. As I kept hearing how bad it this method was, so I figured, let’s see just how terrible it is. And of course, I needed something to compare to, so I decided to try all of the ways I could think of. So I will generate varying numbers of rows with the following methods:

Read on for the list of competitors and how they did.

Leave a Comment

Koen Verbeeck makes use of regular expressions:

I have text stored in my SQL Server database, and I want to split it into all its different words. T-SQL has supported the STRING_SPLIT function since SQL Server 2016, but it only allows one single delimiter. There are multiple possible delimiters, such as commas, spaces, carriage returns and so on. It seems quite cumbersome to nest multiple STRING_SPLIT using APPLY. Is there a better option out there that doesn’t involve custom coding with CLR?

Read on to learn more. My understanding is that this method is similar in terms of performance to the other mechanisms we have available, like STRING_SPLIT(), tally tables, the APPLY operator, and CLR functions.

Leave a Comment

Louis Davidson tries out a function:

Using this function, you can group data easily into different time buckets, like year, month, day (which are standard enough, of course), but also into buckets like 2 days, 6.4 weeks, etc. Now, I don’t feel like this should ever make you decide that you don’t need a date dimension for you warehouse, but it is great when you are are just checking out data and want to play with different intervals in an easy manner.

Read on to see how it works.

Leave a Comment

Jared Westover dives in:

Microsoft added several new features in SQL Server 2025, including an exciting suite of regular expression (regex) functions. After years of anticipation, there’s no longer a need to rely on CLR to use regex capabilities. As an experienced SQL developer, I enjoy finding specific rows, and the function that stands out to me is REGEXP_LIKE. I was drawn to it by its name, mainly because I frequently use the LIKE predicate. Right now, we are deciding whether to use it.

Read on for the results of Jared’s testing.

Leave a Comment

Koen Verbeeck wants to catch them all:

We have a use case where we need to extract a substring of a text. In addition, it needs to match a regular expression (regex). It’s also possible there are multiple matches in one piece of text. How can we efficiently extract those substrings without using a loop or a cursor?

Read on to see how this works.

Comments closed

Ben Johnston shares some thoughts:

I started writing this post with the intention of showing performance details and differences between the legacy TSQL functions and the new regex functions, along with basic functionality examples. There is just too much information to put this into a single post, so I’ve split it. This post covers my initial findings and basic guidelines for using regex functions. The next post is an in-depth look at performance. It compares legacy functions and their equivalent regex functions. I’ve already seen posts covering functionality, so that’s why I’m primarily focused on performance. I spend a good portion of my time on performance tuning, so it’s one of the first questions I ask about a new solution, especially one with functionality that could replace some legacy functionality.

This post is high-level advice for each of the functions. The short version is most of the regular expressions won’t make your code faster, so save them for the cases that are extremely difficult or impossible to solve using classic T-SQL techniques. But there are a couple of winners.

Comments closed

Hugo Kornelis has gotten into the video game:

Below, you find my first video blog. I talk about the OVER clause, and I hope to convince you why you should always explicitly specify the ROWS or RANGE clause – even though that takes a bit more typing.

Understanding what the default frames look like for your queries is important. And it’s important to note that the default frame is different based on whether you use SUM(x) OVER() or SUM(x) OVER(ORDER BY X). It also doesn’t matter for ranking window functions (ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()) because you’re not allowed to specify a window frame.

Comments closed