Press "Enter" to skip to content

Month: July 2025

Regular Expressions in SQL Server 2025

Published 2025-07-15 by Kevin Feasel

Ed Pollack digs into some new functionality:

String-searching in SQL Server has always been a mighty hassle. Balancing performance and horribly-complex queries is a compromise that no one enjoys. 

Generally speaking, a relational database is not an ideal place to search large amounts of text. Even when leveraging features such as Full-Text Indexing, the ability for an application to leverage speedy text-searching decreases as data becomes larger. If a service optimized for text-search can be used, such as Elasticsearch or Azure AI Search, then it will be far easier to deliver accurate results quickly. 

Ed focuses on the mechanisms available rather than performance, and that’s the current sticking point. Whether regular expression queries will get faster in subsequent CTPs or SQL Server 2025 RTM, we’ll see.

Comments closed

Copying Azure SQL Databases between Subscriptions

Published 2025-07-15 by Kevin Feasel

Kenneth Fisher is back in the fight:

I recently had to copy an Azure SQL database (SQL db) from one subscription to an Azure SQL Server instance in another subscription. All of the help I found suggested going to the database and hitting the COPY option. Unfortunately, when I did, I ran into a problem.

Read on for the issue, as well as one way to fix it. The route Kenneth landed on was the same one I ended up going with when I had a similar problem and very limited access to SQL DB on both subscriptions.

Comments closed

Fixing Slow Row-Level Security Policies in PostgreSQL

Published 2025-07-15 by Kevin Feasel

Dian Fay troubleshoots some row-level security slowness:

At my day job, we use row-level security extensively. Several different roles interact with Postgres through the same GraphQL API; each role has its own grants and policies on tables; whether a role can see record X in table Y can depend on its access to record A in table B, so these policies aren’t merely a function of the contents of the candidate row itself. There’s more complexity than that, even, but no need to get into it.

Read on for a dive into row-level security and several tips to make the operation faster.

Comments closed

Adaptive Joins and Equivalent Plans

Published 2025-07-15 by Kevin Feasel

Forrest McDaniel has a public service announcement:

In case you haven’t heard, moral decline is everywhere. The latest generation of query operators is a prime example of this rot. “But this is programming” you say, “what do morals have to do with anything?” No. This is SQL. And for us, we have the term morally equivalent plans which allows me to finally have an ethical excuse for sprinkling a post with degenerate puns.

I bit my tongue specifically to avoid doing exactly what Forrest does. So click through to read all of the terrible puns.

Comments closed

Efficiency of Sparse Hash Tables in PostgreSQL

Published 2025-07-15 by Kevin Feasel

Ashutosh Bapat runs some tests:

The hash_create() API in PostgreSQL takes initial size as an argument. It allocates memory for those many hash entries upfront. If more entries are added, it will expand that memory later. The point of argument was what should be the initial size of the hash table, introduced by that patch, containing the derived clauses. During the discussion, David hypothesised that the size of the hash table affects the efficiency of the hash table operations depending upon whether the hash table fits cache line. While I thought it’s reasonable to assume so, the practical impact wouldn’t be noticeable. I thought that beyond saving a few bytes choosing the right hash table size wasn’t going to have any noticeable effects. If an derived clause lookup or insert became a bit slower, nobody would even notice it. It was practically easy to address David’s concern by using the number of derived clauses at the time of creating the hash table to decide initial size of the hash table. The patch was committed.

Read on to see how things didn’t quite turn out this way, and what the results of testing look like.

Comments closed

LakeBench Now Available

Published 2025-07-14 by Kevin Feasel

Miles Cole makes an announcement:

I’m excited to formally announce LakeBench, now in version v0.3, the first Python-based multi-modal benchmarking library that supports multiple data processing engines on multiple benchmarks. You can find it on GitHub and PyPi.

Traditional benchmarks like TPC-DS and TPC-H focus heavily on analytical queries, but they miss the reality of modern data engineering: building complex ELT pipelines. LakeBench bridges this gap by introducing novel benchmarks that measure not just query performance, but also data loading, transformation, incremental processing, and maintenance operations. The first of such benchmarks is called ELTBench and is initially available in light mode.

Click through to see how it works and grab a copy if you’re interested.

Comments closed

Extending Ola’s Backup Scripts for Scale

Published 2025-07-14 by Kevin Feasel

Aaron Bertrand looks at some extensions to a venerable solution:

Where I work, we have hundreds of mission-critical databases. We use Ola Hallengren’s SQL Server backup solution, but, out of necessity, have wrapped it with several enhancements to help us accomplish goals around retention, SQL Server backup frequency, and recovery point objectives. I touched on some of this last year when I talked about automating some of our restore testing, in It’s a recovery strategy, not a backup strategy. Today, we do a lot more than that, and we have to be more flexible about it.

Click through to see what Aaron’s environment looks like and some of the things they’ve done to make recovery work better.

Comments closed

Looking at PostgreSQL Internals with pg_visibility

Published 2025-07-14 by Kevin Feasel

Ian Parker introduces us to an extension:

When most people think about PostgreSQL internals, they picture tables, indexes, and perhaps the VACUUM process. Tucked away in the contrib extensions, however, is a tool that exposes what really sits on disk: the pg_visibility extension. By querying it, you see how PostgreSQL tracks visibility and freezing at the page level—information that directly affects vacuum efficiency and index-only scans.

Read on to see how this extension works.

Comments closed

The Limits of Transparent Data Encryption

Published 2025-07-14 by Kevin Feasel

Steve Stedman breaks down some myths around Transparent Data Encryption in SQL Server:

Transparent Data Encryption (TDE) in SQL Server is a powerful feature designed to protect data at rest by encrypting database files. While TDE is an excellent tool for securing data stored on disk, it’s often misunderstood, leading to assumptions about its capabilities. In this blog post, we’ll clarify what TDE does not do to help you better understand its scope and limitations, ensuring you implement the right security measures for your SQL Server environment.

Click through for the list.

Comments closed