A Fine Slice Of SQL Server
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 CommentErik Darling has a new video. This one is on variable assignment and how SET and SELECT behave somewhat differently. The crux of the video is how SELECT will not reset a variable’s value the result of the assignment is NULL. Erik also shows how to make SELECT respect NULL in variable assignment.
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 CommentKoen 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.
Leave a CommentSebastiao Pereira has a new way for us to look busy:
Games are by nature fit for randomness, relying on chance like to roll dice, shuffling cards, spawning enemies, and others. Is it possible to use T-SQL to accomplish the randomness needed? Let’s see how we can create a SQL Server poker game using a random numbers generator process.
Read on to see how it works.
Comments closedBen 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 closedHugo 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.
Jared Westover creates a census:
We need to find out how many times certain strings appear in a column. We’ve used the LEN and REPLACE functions for years, but recently heard about REGEXP_COUNT and want to evaluate it, since we plan to upgrade to SQL Server 2025. How can we test this new feature?
Read on to see how it works.
Comments closedAndy Brownsword checks some data types:
Last week we looked at how expressions are evaluated with the
ISNULLandCOALESCEfunctions. 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
ISNULLtoCOALESCEand expect the same result.
Read on to see how the two behave under different circumstances with changing data types.
Comments closedTomaz 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.