A Fine Slice Of SQL Server
Sebastiao 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.
Leave a CommentBen 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 CommentHugo 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.
Leave a CommentAndy 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.
Leave a CommentTomaz 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.
Aaron Bertrand continues digging into SQL Server pagination performance:
In my previous tip, Pagination Performance in SQL Server, I showed how to make SQL pagination more predictable – turning
O(n)intoO(1). I materialized and cached row numbers to page through instead of calculating them on every request. It wasn’t the whole story, though; real pagination queries rarely get to sort without filtering. Users always want more control, and filtering can threaten that predictability.
Read on for examples of how to handle a few different scenarios.
Leave a CommentAaron Bertrand takes life one page at a time:
Many web applications and APIs implement pagination to control how much data is returned or displayed. Many paging solutions suffer from the linear scaling problem (often referred to as
O(n)), meaning the amount of work increases as you get into higher page numbers. If a user has ever clicked “next page” or “last page” and your CPUs caught on fire, you may have been a victim of linear scaling. Are there any creative solutions that will achieve constant-time performance (O(1))?
Aaron’s answer is interesting, particularly if you’re able to define the valid set of filters. At a prior job, I was responsible for filtering of arbitrary combinations of 30+ different columns across multiple dimensions and a fact table in a warehouse. That was a royal pain. The best we could do was run the query once, using ROW_NUMBER() to capture the sort order, and then store that ordering in a specialized table with an identifier token that was a hash of the incoming session info, and cache that data for a pre-set amount of time—which, if I remember correctly, was 5 minutes. Somewhat similar to what Aaron shows but much more ephemeral and it caused the first load to be consistently slower while making subsequent paging activities much faster.
When eliminating
NULLvalues with SQL Server queries we typically reach forISNULLorCOALESCEto do the job. Generally speaking they’ll provide equivalent results, but they work in different ways. If you’re dealing with logic more complex thanISNULL(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
NULLvalues 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 evaluatingNULLvalues.
Read on for the performance showdown.
Leave a CommentJovan Popovic makes an announcement:
We have introduced a new DATE_BUCKET() function in Fabric Data Warehouse SQL language that makes reporting and analytics even easier.
In this blog post, you’ll discover how it simplifies time-based reporting and makes grouping dates effortless.
My experience is that DATE_BUCKET() takes a bit of effort getting used to, as it is not an intuitive function. That said, it can be really powerful for dealing with time series data. It is also available in SQL Server, as of SQL Server 2022.