Press "Enter" to skip to content

Category: Syntax

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.

Leave a Comment

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.

Leave a Comment

Contrasting ISNULL() versus COALESCE() Performance

Published 2025-12-22 by Kevin Feasel

Andy Brownsword takes a peek:

When eliminating NULL values with SQL Server queries we typically reach for ISNULL or COALESCE to do the job. Generally speaking they’ll provide equivalent results, but they work in different ways. If you’re dealing with logic more complex than ISNULL(ColA, ColB) – for example using a function or subquery as part of the expression – then you might be in for a surprise.

The content of expressions when evaluating NULL values can have big implications on query performance. In this post we’ll look at how the functions work and the implications they can have when evaluating NULL values.

Read on for the performance showdown.

Leave a Comment

RegEx Performance in SQL Server 2025

Published 2025-12-11 by Kevin Feasel

Brent Ozar has an update:

Back in March 2025 when Microsoft first announced that REGEX support was coming to SQL Server 2025 and Azure SQL DB, I gave it a quick test, and the performance was horrific. It was bad in 3 different ways:

  1. The CPU usage was terrible, burning 60 seconds of CPU time to check a few million rows
  2. It refused to use an index
  3. The cardinality estimation was terrible, hard-coded to 30% of the table

Read on to see what has changed. It’s obviously not perfect, but just as obviously is much better than what Brent saw in Azure SQL DB at the time.

Leave a Comment

Regular Expression Functions in SQL Server 2025

Published 2025-12-10 by Kevin Feasel

Tomaz Kastrun continues an advent of SQL Server 2025. Day 8 looks at a pair of regular expression-related functions:

Continuing with SQL Server 2025 T-SQL functions for Regular Expressions for in string and count functionalities.

And Day 9 hits two more:

Last two functions in the family of new T-SQL functions that were shipped with RegEx, are REGEXP_MATCHES() and REGEXP_SPLIT_TO_TABLE().

Read on to see how all four of these work.

Leave a Comment

Text Search in PostgreSQL

Published 2025-12-10 by Kevin Feasel

Jay Miller is looking for strings in all the wrong places:

I like to think of this like seeing a doctor. You can go to a family doctor and they can help you with most things. For specific results, it’s better to see a specialist who has a better understanding of the particular issue.

Search is the same way. At the end of the day, you get results but what you put into your search will affect what you get. That said there are some search methods that work better than others depending on your data.

Read on for several techniques that are available. I do think the headers denigrate LIKE/iLIKE a bit too much, as it works pretty well in many circumstances. But there are definitely good times to bring out the more powerful mechanisms, as this article shows.

Leave a Comment

New T-SQL Functions

Published 2025-12-08 by Kevin Feasel

Tomaz Kastrun has been busy with this year’s advent of SQL Server 2025 blog posting. Catching up, Tomaz first looks at base-64 encoding and decoding:

SQL Server 2025 introduces a new T-SQL functions for BASE64 varbinary expressions. The first function returns base64-encoded text (BASE64_ENCODE() ), respectively for BASE64_DECODE().

BASE64_ENCODE converts the value of a varbinary expression into a base64-encoded varchar expression.

BASE64_DECODE converts a base64-encoded varchar expression into the corresponding varbinary expression.

I really like this, by the way. Base-64 encoding is quite useful for web transmissions, so having a place to generate that output easily is nice.

Second up is REGEXP_LIKE():

SQL Server 2025 introduces a new T-SQL functions for Regular Expressions (RegEx).

With multiple RegEx functions, the LIKE function indicates if the regular expression pattern matches the string or is in a string. The function is REGEXP_LIKE() that will do the job.

And third, we have REGEXP_SUBSTR() and REGEXP_REPLACE():

Continuing with SQL Server 2025 T-SQL functions for Regular Expressions for replace and substring functionalities.

Click through for Tomaz’s thoughts on all five of these functions.

Comments closed

Comparing TRANSLATE() and REPLACE()

Published 2025-12-05 by Kevin Feasel

Louis Davidson is lost in translation:

The data I am working with sometimes has people with multiple parts to their name (Mary Jo, Cindy Lou) etc, or sometimes Fred/Joe, Mary and Jack, Mary & Jack, or what have you. My goal was to turn these names into little delimited lists that I could parse on a space character with STRING_SPLIT and there were a “few” of these cases. This was the code I had arrived at when I reached the “good enough” stage of my coding.

Louis had 19 nested REPLACE() calls, but Certified Good Guy Erik Darling shows him the way.

Comments closed