Press "Enter" to skip to content

Curated SQL Posts

Optimizing Polymorphic Associations in Postgres

Published 2026-06-23 by Kevin Feasel

Andrei Lepikhov continues a thread:

Recently, I looked into how common polymorphic associations actually are in relational databases — a performance-hostile pattern built around a discriminated foreign key that ORMs (Rails, Django, Hibernate), CRM platforms (Salesforce), and 1C generate automatically. The front page of a typical online store, or the activity feed of a CRM, is built by exactly this kind of query: a base table is LEFT JOIN-ed to every possible subtype through a (type, id) pair of columns.

That earlier article answered the question ‘how widespread is this pattern?’ After all, if you’re going to improve something, it helps to know how useful the improvement will be, right? Here, I want to give a sense of how this pattern leads to performance regressions and point out directions in the PostgreSQL optimiser that could make the situation easier.

Much of this is speculative in nature but the three proposed solution ideas are all interesting.

Leave a Comment

Patched SQL Injection Vulnerability in sys.sp_dbmmonitorupdate

Published 2026-06-23 by Kevin Feasel

Fabiano Amorim digs into a fixed issue:

What makes this case particularly interesting is not just that the vulnerability exists in a trusted system object, but how it works: the injection bypasses a REPLACE-based sanitization attempt through a subtle Unicode character conversion that happens silently during a variable assignment.

The vulnerability was reported to Microsoft and they have since fixed it, but it’s still worth exposing and explaining given how intricate it is. So, that’s what I’ll do in this article.

Click through to see how it works. And of course this database mirroring stored procedure is still hanging around long after database mirroring itself was deprecated. But that’s the downside to deprecation without subsequent removal.

Leave a Comment

The State of SQL Notebooks

Published 2026-06-22 by Kevin Feasel

Deborah Melkin takes a look at the state of notebooks in the Microsoft ecosystem:

Azure Data Studio, as some of you know, has now been deprecated. That came out a while ago and the official deprecation was, I think, a couple months ago at this point. It’s all a blur, but needless to say, no more Azure Data Studio. I had gotten an email from someone who said they saw my presentation and they’d love to see more about it, especially with VS Code. Because there was an extension in VS Code for notebooks, and particularly something called .NET Interactive, which are polyglot notebooks, polyglot multi-language. All right, that was really cool and I had started addressing that, too, because it had just been introduced. It’s a really cool concept.

And then before I had a chance to put it together, new notice from Microsoft. Guess what’s being deprecated?

You guessed it. .NET Interactive notebooks. They went bye-bye. Great.

But wait, I hear SQL Notebook’s theme music?

Leave a Comment

User-Defined Functions and Power BI Testing

Published 2026-06-22 by Kevin Feasel

John Kerski is excited:

User Defined Functions (UDFs) are, in my opinion, the biggest update to Power BI Desktop since PBIP.

That may sound dramatic, but if you care about DataOps, semantic model quality, and reusable development patterns, UDFs fundamentally change what is possible with DAX.

Reuse is one of the core principles of DataOps. For years we have been able to build reusable patterns in Power Query, PowerShell, Python, YAML, and infrastructure automation. But DAX was always missing a key capability: reusable logic that could live inside the semantic model itself.

Until now.

Read on to learn more, as well as to get a link to John’s PQL.Assert DAX unit testing library.

Leave a Comment

T-SQL Tuesday 199 Round-Up

Published 2026-06-22 by Kevin Feasel

Koen Verbeeck wraps up another T-SQL Tuesday:

A good week ago I hosted the monthly T-SQL Tuesday blog party. I invited the community the blog about the idea of what would happen if we would need to go back on-premises, after a good decade of cloud computing. What would you need to do? How do you plan for this? What are the skills you might need to (re-)learn? You can find my take on this here, where I’m telling my junior colleagues that troubleshooting issues on-prem is more challenging, because it’s possible you need to go way down, all the way to Kerberos, SAN and DNS issues.

Click through for the contributions.

Leave a Comment

Moving Away from Average Fragmentation for Index Maintenance

Published 2026-06-22 by Kevin Feasel

Tim Radney proffers some advice:

As a SQL Server DBA with years of experience tuning production environments, I’ve seen the same patterns repeat: nightly index maintenance jobs running for hours, consuming massive CPU and I/O, only for performance to remain inconsistent or even degrade in some cases. Many of us (myself included, early in my career) relied heavily on avg_fragmentation_in_percent from sys.dm_db_index_physical_stats to decide when to reorganize or rebuild indexes. It’s time we move past that outdated approach and stop just doing what we’ve always done.

One thing I would add on to Tim’s advice is, determine whether that index even needs rebuilt or if you’re burning resources for no practical benefit. If there’s no practical performance benefit from the operation—and with all-flash arrays that are within an order of magnitude of RAM speeds, you might already be close to that point—then the index rebuild is for naught. The index maintenance strategy that we all learned back in the day was to minimize the time we spent waiting for spinning disks to reach relevant data. When random access lookups are approximately the same speed as sequential lookups, position on the disk doesn’t matter all that much.

Leave a Comment

Using Entra ID Authentication in Azure Database for PostgreSQL

Published 2026-06-19 by Kevin Feasel

Taiob Ali wants to use Entra for authentication:

During the livestream of my session at the POSETTE: An Event for Postgres 2026 conference, I received the following question on the hallway track via Discord:

I have one question : I added an Entra group as PostgreSQL Entra administrator, created/mapped the PostgreSQL role for that group, and granted permissions to that group. I am a member of the Entra group, but I still cannot log in with my own Entra user. Does Azure PostgreSQL allow group members to authenticate through the group role, or must each user also be individually created/mapped as a PostgreSQL role?

Read on for Taiob’s full answer.

Leave a Comment

Major Announcements from Microsoft Build 2026

Published 2026-06-19 by Kevin Feasel

James Serra puts together a list:

Once again there were a number of Microsoft Build announcements related to data and AI, and some were very impressive. Below are my favorites. I am prioritizing the data announcements first, because that is where my brain naturally goes (and because AI without good data is just a very confident intern with access to a keyboard).

The biggest announcements across Microsoft Fabric and databases can be found in the Microsoft Build 2026: Building agentic apps with Microsoft Fabric and Microsoft Databases blog post.

This looks like another year of Fabric + AI as the (almost) entire data platform focus at Build.

Leave a Comment