Press "Enter" to skip to content

Category: Syntax

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

Thoughts on Parallel Programming in T-SQL

Published 2025-12-23 by Kevin Feasel

Greg Low shares some thoughts:

Upcoming processors are likely to have even more cores than now. Have you ever tried to write multiprocessor-style code? A friend of mine recently said that he learned some of this style of coding but later when he came back to it, he realised how much he thought he knew but didn’t.

For languages like T-SQL, we don’t have inherent support for multi-threading. In fact, the only trace I can see of this in T-SQL today is the ability to have multiple readers on a service broker queue.

In general, we haven’t needed this because SQL Server systems constantly have many requests thrown at them concurrently anyway and there is a natural style of parallelism happening.

I’d take it one step further. T-SQL, as a reasonable attempt at a 4th-generation programming language, abstracts away the need to define what should or should not be parallel. That’s the job of the database engine. We tell it what the end result should look like and let the engine figure out the details.

I do like the idea that Greg mentions of running stored procedures asynchronously. That’s something we typically need a separate programming language and some calling code to implement. Either that or a larger number of SSMS tabs.

Comments closed

Text Features in SQL Server 2025

Published 2025-12-23 by Kevin Feasel

Tomaz Kastrun continues an advent of SQL Server 2025. Day 22 looks at the UNISTR() function:

UNISTR() function is a new T-SQL function in SQL Server 2025. It will help you with unicode string literals (e.g.: special characters, emoji, special language alphabets and others) by letting you specify the unicode encoding value of characters in the string.

Difference between NCHAR and UNISTR is that latter will provide more flexibility and ways of handling multiple unicode characters and even escape sequences. You can also define a custom escape character to perform the necessary conversion of Unicode values into a string character set.

Day 23 looks at a new way of concatenating and compound assigning:

Two new features are available in SQL Server 2025 for string operations; both for string concatenation.

The || and ||= combo are basically + and += for string, but it brings T-SQL in alignment with ANSI SQL. I’d still recommend using functions like CONCAT() for NULL-safety, or CONCAT_WS() for NULL-safety plus automatic separator addition, but it does fix a longer-standing pain point around platform compatibility.

Comments closed