Press "Enter" to skip to content

Author: Kevin Feasel

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

Vector Search: Negation and Cross-Encoding

Published 2025-12-23 by Kevin Feasel

Joe Sack digs into a common problem in vector search. First up is a description of the problem:

I embedded two queries: “home with pool” and “home without pool.” The cosine similarity was 0.82. The embedding model treats negated queries as nearly identical to their positive counterparts.

For comparison, completely unrelated queries (“home with pool” vs “quarterly earnings report”) scored 0.13.

And one imperfect solution:

In my last post, I showed that vector search treats “home with pool” and “home without pool” as nearly identical (0.82 similarity). Bi-encoders struggle with negation.

Cross-encoders can help with this. But there’s a trade-off.

Read on to learn how cross-encoders can help, but they come at a significant cost. Joe also describes a pattern that can minimize the total pain level when using cross-encoders.

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

Privilege Escalation via Replication Job

Published 2025-12-23 by Kevin Feasel

Fabiano Amorim makes note of a security concern:

Privilege escalation in SQL Server isn’t just theory – it can happen through everyday maintenance jobs. This article demonstrates how a user with roles like db_owner or db_ddladmin can exploit replication cleanup processes to gain sysadmin rights, and why monitoring trigger creation and job behavior is critical for security.

Replication is one of those things people tend not to understand very well, including the necessary permissions. It’s a lot easier simply to say, “Here’s sysadmin” because that actually works instead of giving you a cryptic error you can barely troubleshoot and that’s only thanks to a Repltalk article from 2009. And heaven help you if you’re looking at merge replication.

But as far as the article goes, I won’t say that it’s much ado about nothing. What I will, however, say is that your account needs to be db_owner or db_ddladmin first, and that does mitigate a fair amount of the risk.

Leave a Comment

The Impact of Sorting and Filters on Pagination

Published 2025-12-23 by Kevin Feasel

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) into O(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 Comment

Budgeting in Azure

Published 2025-12-23 by Kevin Feasel

John Morehouse breaks out the envelopes:

When organizations migrate workloads to Azure, the focus is usually on architecture, performance, and security. Cost management should be part of that conversation—but in practice, it’s often treated as an afterthought. One of the most overlooked and underutilized tools in Azure is Budgets, despite the fact that it can prevent unpleasant billing surprises with minimal effort.

Azure budgeting is useful but not great. I think it relies too much on messaging without enough teeth, so that requires setting up runbooks and humans constantly reacting rather than being able to set stronger rules around scaling down resources prior to getting that unexpected and unwelcome surprise bill.

Leave a Comment

Consistent Pagination Performance in SQL Server

Published 2025-12-22 by Kevin Feasel

Aaron 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.

Leave a Comment

Accessing Microsoft Graph API via Fabric Data Factory

Published 2025-12-22 by Kevin Feasel

Paul Hernandez makes a connection:

This article is an updated version of my 2022 post on using Synapse pipelines to retrieve security groups and their members through the Microsoft Graph API. Some customers recently asked for a Microsoft Fabric–based approach, and I also noticed that many developers are still defaulting to Python clients to interact with Graph. While Python works perfectly fine, this walkthrough demonstrates how you can accomplish the same using a parameterized Copy Data activity inside a Fabric Data Factory pipeline.

Read on to see how.

Leave a Comment

Diagnosing SQL Audit Failure

Published 2025-12-22 by Kevin Feasel

Alyssa Montgomery troubleshoots an issue:

Message: 

SQL Server Audit failed to create an audit file related to the audit ‘AuditName_ServerAudit’ in the directory ‘C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log’. Make sure that the disk is not full and that the SQL Server service account has the required permissions to create and write to the file. 

Based on the error, the solution would be to free up drive space or add user/service account permissions in the file path. Unless you are initially setting up an audit, typically permissions are not the issue. 

Read on for an example and how to resolve this issue.

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