Press "Enter" to skip to content

Category: Syntax

String Splitting to Table with Regular Expressions in SQL Server 2025

Published 2026-01-27 by Kevin Feasel

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

Fun with DATE_BUCKET()

Published 2026-01-22 by Kevin Feasel

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

A Deep Dive into PostgreSQL Arrays

Published 2026-01-14 by Kevin Feasel

Radim Marek talks arrays:

The official documentation provides a good introduction. But beneath this straightforward interface lies a set of more complex properties than most of us realise. Arrays in PostgreSQL are not just “lists” in a field. They have their own memory management strategy, their own index logic, and a lot of edge-case scenarios.

As it goes with boringSQL deep-dives, this article will explore the corners of array functionality that might break your production.

Click through for some not-boring explanation around arrays in PostgreSQL.

Leave a Comment

Using REGEXP_LIKE in SQL Server 2025

Published 2026-01-12 by Kevin Feasel

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

Table Renames and Views in PostgreSQL

Published 2026-01-06 by Kevin Feasel

Deepak Mahto runs into a common issue:

Instead of doing a straight:

ALTER TABLE ... ADD COLUMN ... DEFAULT ... NOT NULL;

we chose the commonly recommended performance approach:

  • Create a new table (optionally UNLOGGED),
  • Copy the data,
  • Rename/swap the tables.

This approach is widely used to avoid long-running locks and table rewrites but it comes with hidden gotchas. This post is about one such gotcha: object dependencies, especially views, and how PostgreSQL tracks them internally using OIDs.

This also happens in SQL Server, though we do have a helpful sp_refreshview procedure that means not needing to drop + recreate views.

Comments closed

SQL Server 2025 Regular Expression Guidelines

Published 2026-01-02 by Kevin Feasel

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

Default Frames in Window Functions

Published 2026-01-02 by Kevin Feasel

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

ISNULL() and COALESCE() Return Types

Published 2025-12-29 by Kevin Feasel

Andy Brownsword checks some data types:

Last week we looked at how expressions are evaluated with the ISNULL and COALESCE functions. Whilst we’re in the area it’s worth running through how data types are selected for them too. It might have implications with how you implement them.

The way these functions choose which data type to return differs, so they aren’t direct swap outs for each other, and you can’t simply ‘upgrade’ an ISNULL to COALESCE and expect the same result.

Read on to see how the two behave under different circumstances with changing data types.

Comments closed