A Fine Slice Of SQL Server
Koen 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 CommentDeepak 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.
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 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 CommentGreg 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 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.
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 CommentBack 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:
- The CPU usage was terrible, burning 60 seconds of CPU time to check a few million rows
- It refused to use an index
- 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.
Comments closed